How to design category and subcategories in MySQL?

Question

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.)

Answer 1

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

Answer 2

If you are using mysql and/or sqlite (which you both have in your tags), doesn't offer any constructs for recursive queries, chances are you will be better of with a nested set model rather than a parent/child relationship.

It might be massively overkill, or it might not be fit for purpose (if its more heavy on inserts than reads), but nevertheless, its fun to learn so give these a read

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

http://en.wikipedia.org/wiki/Nested_set_model