What kind of DB structure should I use for a site has infinite sub-category?

Question

For example, "Dole Banana" is a kind of product, it's listed under the "Bananas" category, when I open the "Fruits" category, I want to see "Dole Banana".

+ Food
|--+ Fruits
|------+ Bananas   
|------+ Apples
|--+ Vegetables
|------+ Onion
|------+ Spinach

Answer 1

I've usually used left-right trees which are very well adapted to database querys. You have a parentId,left and right value for each node. Every nodes children has a left/right value that is between the parent nodes left and right which makes it very easy to find for example all children/parents of a node. It does give a slight overhead on insertions, but it shouldn't be too much of an impact unless you insert alot.

Edit: Just a word of warning though, you need to make the insert/update operations in a locked transaction or the tree can get messed up.

Answer 2

If you're looking for online resources that address this problem, "Storing a Tree in a Database" would be a good search phrase.

As for the solution, note that each subcategory can have either one or zero parent categories. Therefore, the entire tree can be stored in a single self-refferental table with a "parent" field.

Using your example tree:

 ID  | PARENT | NAME
-----+--------+-------------
  1  |  null  | Food
  2  |   1    | Fruits
  3  |   2    | Bananas
  4  |   2    | Apples
  5  |   1    | Vegetables
  6  |   5    | Onion
  7  |   5    | Spinach