Mysql 根类别和子类别,如何在MySQL中设计类别和子类别？

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

解决方案

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