How do I create nested categories in a Database?

Question

I am making a videos website where categories will be nested:

e.g. Programming-> C Language - > MIT Videos -> Video 1 Programming -> C Language -> Stanford Video - > Video 1 Programming -> Python -> Video 1

These categories and sub-categories will be created by users on the fly. I will need to show them as people create them in the form of a navigable menu, so that people can browse the collection easily.

Could someone please help me with how I can go about creating such a database?

Answer 1

Make a categories table with the following fields:

• CategoryID - Integer
• CategoryName - String/Varchar/Whatever
• ParentID - Integer

Your ParentID will then reference back to the CategoryID of its parent.

Example:

CategoryID CategoryName ParentID
---------------------------------
1          Dog          NULL
2          Cat          NULL
3          Poodle       1
4          Dachsund     1
5          Persian      2
6          Toy Poodle   3