php - my sql multiple category-subcategory-subcategory

Question

category subcategory subcategory
jewelry   body       nose ring,arm ring,ear ring 
          men        ring,ear ring

I have multiple category->subcategory->subcategory so how will be the table for this in MySQL?

Answer 1

Structure your Table like this:

Id   Category    ParentId
1     Jewelry     NULL
2     Body          1
3     nose ring     2
4     arm ring      2
5     ear ring      2
- 
-

This is called Self-Referencing Table i.e. ParentId columns contains either NULL or value from the Id column of same table.

so whenever you have to know all the direct subcategories of a given category, you simply create a query like :

   Select * from CategoryMaster where ParentId = 2;

doing this you will get all the sub-categories for the sub-category Body.

Now, the best part about this data-structure is that you can have n-levels of sub-categories for any given sub-category and same single table with 3 columns (at minimum) will do.