Categories and Subcategories Table Structure - mysql

Question

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 1

Categories and sub-categories are both just logical 'categories' so there's no need to implement more than one physical table unless your design has a specific requirement to do so (although I can't think of one right now...).

Additionally, there's no need to have a structure specifically for each level in your hierarchy; a single table with a self referential join back to the parent at each 'category' (or 'sub-category') level is all you need.

I'm assuming in this example that your categories (or sub-categories) may contain elements so:

// categories table 
category_id
category_name
element 
parent_category_id

This solution is elegant and allows for expansion of your hierarchy without changing the table.

DML for top level categories are made with the predicate "parent_category_id IS NULL".

DML for lower level categories (or sub-categories) are made with the predicate "parent_category_id = 'whatever the parent category ID is'".

Cheers Scott

P.S. I don't have enough rep to respond directory to Sherif's answer, and I might be (read probably am) missing something, but a modified preorder transversal (or nested set) can also be implemented with a single table; method and comparison with Adjancey list are described well in this document

I do agree with the pros and cons of each method as described by Sherif.

Answer 2

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