How to design category and subcategories in MySQL?
database, mysql, sql, sqlite
Solution
You could have everything in one table, category. Then have a column for parentID. If parentID = 0, it is a master category, if its another ID, then it is a subcategory? This structure would support sub-sub categories... not sure if that's helpful to you.
Example fields:
Table: category
categoryID
parentID
name
Example data:
categoryID : 1
parentID : 0
name : hot
categoryID : 2
parentID : 0
name: cold
categoryID : 3
parentID : 2
name : a soup that's cold
categoryID : 4
parentID: 1
name: a soup that's hot
Problem
I have a list of categories and number of sub categories associated to each category. let say Category table is called Cat then I have hot and cold categories in it I have another table called subcats then I have the following: Cat: ``` ID Name 1 Hot 2 Cold ``` SubCats: ``` SubCatID CATID Name 1 1 soup 2 1 rice 3 1 pizza 4 2 salad 5 2 fruit ``` I should consider performance in my design, how do you rate my design? is there any better solution? (Categories are just sample - I have heaps of categories and subcategories.)