S.M_Emamian S.M_Emamian - 3 months ago 17
MySQL Question

Categories and Subcategories Table Structure - mysql

An admin can create multi category and subcategory.

for example what admin can :

cat1 cat2
->sub1 ->sub1
->sub2 ->sub1_1
->sub3
->sub3_1
->sub3_2
->sub3_2_1
->sub3_2_2
->sub4
sub4_1
-sub5


I don't know what is the best choice? I should a table or two tables ?

It's what I know:

id title parent_cat_id

1 cat1 0
2 sub1 1
3 sub2 1
3 sub3 1
4 sub3_1 3
5 sub3_2 3
6 sub3_2_1 5
7 sub3_2_2 5
...

Answer

Best is a relative term, but we can certainly explore the trade-offs of the available solutions to storing hierarchical data structures in a relational DBMS. Relational databases tend to be flat structures, because they're typically normalized as 2-dimensional tables, made up of columns interesting with rows. This makes the very idea of a nested structure rather difficult to store in such a database.

There are two common ways to store this type of data in your relational database. Specifically, as an adjacency list, or using modified preorder traversal.

  • Adjancey list approach

    • Pros

      • Easy to implement and understand
      • You can easily migrate a large sub category into another parent
      • No huge cost of writes to the database no matter how large the structure grows
    • Cons

      • A lot more expensive to read (requires a lot of iteration/recursion to rebuild the structure)
      • Easy to break your data in SQL (SQL doesn't do recursion)
  • Using preorder traversal

    • Pros

      • Easier to find all parents in SQL
      • Easier to not break the data in SQL
      • Cheaper to build the entire tree
    • Cons

      • Slightly harder to implement
      • Cost of insertion/update grows as the structure deepens

Now, in your case specifically, it doesn't matter if you have the title column in the table that stores your id and parent_id, because you depend entirely on the primary key of the parent_id.

The adjacency list approach is similar to the one you're stating in your question. You don't need two tables to do that, because you use the id and parent_id fields as an adjacency list to compose and decompose the structure.

The preorder traversal does require using two tables (one to store the categery name or title in your case, and one to store the relationship of a parent to its children). Instead of storing the id, and parent_id in each row, you need to store the left and right child nodes of every parent node.

categories table
----------------
id    title
1     cat1
2     subcat1_1
3     subcat1_2
4     subcat1_2_1



Relationship table
------------------
parent_id      left      right
------------------------------
1              2         3
3              4         NULL