There are categories and sub categories. How will it be better and more correct to do it in two or three tables?
enter image description here

enter image description here

  • one
    If you want to make an infinite tree menu, then id, name, parent and all this through recursion - Urmuz Tagizade
  • Throw out the subcatecories in both the first and second cases. In the first parent_id add parent_id to the categories with a link to id . In the second subcategories_id let it also refer to id from categories_copy1 . Yes. In SQL, it is normal to refer from the table to itself. So fig you extra tables subcategiries ? And if subcategories have subsubcategories , you will add tables subsubsubsub...subcategories ? - Sergey

2 answers 2

One option where a sub-category can only be in one category.

 +------------+ | categories | +------------+ | id |<---. | parent_id |>>--' | name | +------------+

Another universal option is when sub-categories can be in different categories.

 +------------+ +-------------------------+ | categories | | category_subcategories | +------------+ +-------------------------+ | id |<---<<| category_id | | name | `-<<| subcategory_id | +------------+ +-------------------------+

Establishing certain restrictions (a unique key by subcategory_id in category_subcategories) can be achieved so that the second option works as the first: each sub-category is only in a category.

It differs from yours by the absence of sub-category tables. Sub-categories are also categories, so why exacerbate unnecessary tables? Less tables - stronger sleep.
The depth is unlimited. Want - sit with your two, want - grow deep.

    It depends on whether you need to include the same subcategory in two or more other categories. If you need this opportunity you will have to use three tables.

    If each category can have only one parent, then it is better to use the first option with two tables. When the amount of categorized data reaches a couple of gigabytes - the first option will be easier for you - it is more convenient for optimization, since in most queries you can do without IN and indexes in a covering mode.