How to generate URL correctly?

Question

PHP

Hello . Tell me how to form the URL correctly, or whether my way of thinking is generally correct. The essence of the task:
There is a website, products on the site, products have categories, categories have subcategories (there may be several). For example: category - phone, subcategories - used, smartphone, product - Nokia 3250. The URL should be: site name/phone/bu/smartphone/Nokia 3250 . How can I correctly create tables in the database - so that the minimum number of queries to the database would be performed, I now have this option: 1 table - categories (fields: categotyID, categoryName, categoryParentId, categotyUrl) 2 table - product (fields: IDtovara, categoryID well, product description and so on). And how to generate this URL? thanks in advance

Answer 1

As I understand it, the main problem is the least expensive formation of the subcategories list line?
If there is a limit on the maximum possible number of categories for a product, then the easiest option is probably to form a complete list of categories in one SQL query, linking the category table with JOINs as many times as necessary. If not, then you can consider, for example, a way to store hierarchical data in a database called materialized path. There, of course, there are some difficulties with adding and moving subtrees within the tree, but the formation of a list of categories will be very simple. At one time, there seemed to be an overview article on how to store hierarchical data in relational databases.

Answer 2

At one time, I did something similar, as a result, I pulled out all the categories and built a tree with a recursive function. since the engine was written on top of Zend itself - a method was added in the admin panel - when adding new categories, the old tree was crashed in the cache and the new finished tree was saved, it worked quickly and simply. Although of course it's not true)