An admin can create multi category and subcategory.
for example what admin can :
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
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.
Now, in your case specifically, it doesn't matter if you have the
title column in the table that stores your
parent_id, because you depend entirely on the primary key of the
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
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
parent_id in each row, you need to store the
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