I have a project to create an e-commerce, in which my doubt refers to the modeling of the tables:
Category (ROOT)> sub-categories> sub-categories of the sub-category.
How should these tables be, where a product can be within categories (root), and in them may also be in sub-categories. The tables would have to be able to work like the print below, it follows:
HereisanexampleofwhatIdid:
Categories with Sub-Categories, I usually make a single table. In this table I add a idCategoriaPai field.
When making a category record I leave this field null . When I am going to register a subcategory, I populate this field with the id of the parent category.
If this field is filled in, the category is a subcategory. If it is not, the category is the root category.
If you need to return the name of all the parent categories of your sub-category, just do a recursive function.
In this way, you can do various levels of sub-categories.
I do not know if it's the best way, but it's the way I usually work.
