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

Solution 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

Solution 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

Solution 3

There's really nothing wrong with your table structure. It's well normalized and you've implemented it in a standard way. It would probably be a good idea to have a foreign key constraint across the tables (if you don't already).

As Zeke said, however, your design wouldn't support multiple levels of sub-categories, but as long as you know there will only be categories and sub-categories, it's fine.

If you did want "sub-sub-categories" (to an infinite degree), you may just want one table like this:

Cat:

CatID ParentCatID Name
1     null        Hot
2     1           Soup
3     1           Coffee
4     3           Decaf Coffee
5     null        Cold
6     5           Iced Tea

Notice that Coffee's parent ID is "Hot" and Decaf Coffee's ParentID is "Coffee". So Hot > Coffee > Decaf Coffee. Anything with a ParentCatID of null will be a top-level category.

You can have a Foreign Key that references it's own table. So you can create a foreign key between ParentCatID and CatID.