[ACCEPTED]-How do I create nested categories in a Database?-categories

Accepted answer
Score: 17

Make a categories table with the following 2 fields:

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

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


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

Quassnoi said :

You should use either nested 22 sets or parent-child models.

I used to implement 21 both of them. What I could say is:

Use the 20 nested set architecture if your categories 19 table doesn't change often, because on a 18 select clause it's fast and with only one 17 request you can get the whole branch of 16 the hierarchy for a given entry. But on 15 a insert or update clause it takes more 14 time than a parent child model to update 13 the left and right (or lower and upper in 12 the example below) fields.

Another point, quite 11 trivial I must admit, but:
It's very difficult 10 to change the hierarchy by hand directly 9 in the database (It could happen during 8 the development). So, be sure to implement 7 first an interface to play with the nested 6 set (changing parent node, move a branch 5 node, deleting a node or the whole branch 4 etc.)

Here are two articles on the subject:

Last 3 thing, I didn't try it, but I read somewhere 2 that you can have more than one tree in 1 a nested set table, I mean several roots.

Score: 5

You should use either nested sets or parent-child models.


typeid parent name

1      0      Buyers
2      0      Sellers
3      0      Referee
4      1      Electrical
5      1      Mechanic
FROM    mytable
WHERE   group IN
        SELECT  typeid
        FROM    group_types
        START WITH
                typeid = 1
        CONNECT BY
                parent = PRIOR typeid

will 4 select all buyers in Oracle.

Nested sets:

typeid lower  upper  Name
1      1      2      Buyers
2      3      3      Sellers
3      4      4      Referee
4      1      1      Electrical
5      2      2      Mechanic
FROM    group_types
JOIN    mytable
ON      group BETWEEN lower AND upper
WHERE   typeid = 1

will select all buyers 3 in any database.

See this answer for more detail.

Nested sets is 2 more easy to query, but it's harder to update 1 and harder to build a tree structure.

Score: 5

From the example in your question it looks 19 like you'd want it to be possible for a 18 given category to have multiple parents 17 (e.g., "MIT Videos -> Video 1 Programming" as 16 well as "Video -> Video 1 Programming"), in 15 which case simply adding a ParentID column 14 would not be sufficient.

I would recommend 13 creating two tables: a simple Categories 12 table with CategoryID and CategoryName columns, and 11 a separate CategoryRelationships table with 10 ParentCategoryID and ChildCategoryID columns. This 9 way you can specify as many parent-child 8 relationships as you want for any particular 7 category. It would even be possible using 6 this model to have a dual relationship where 5 two categories are each other's parent and 4 child simultaneously. (Off the top of my 3 head, I can't think of a great use for this 2 scenario, but at least it illustrates how 1 flexible the model is.)

Score: 0

What you need is a basic parent-child relationship:

Category (ID: int, ParentID: nullable int, Name: nvarchar(1000))


Score: 0

A better way to store the parent_id of the 7 table is to have it nested within the ID e.g

100000 6 Programming 110000 C Language 111000 Video 5 1 Programming 111100 C Language 111110 Stanford 4 Video

etc..so all you need it a script to 3 process the ID such that the first digit 2 represents the top level category and so 1 on as you go deeper down the hierarchy

More Related questions