[ACCEPTED]-How do I create nested categories in a Database?-categories
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
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.
You should use either
nested sets or
typeid parent name 1 0 Buyers 2 0 Sellers 3 0 Referee 4 1 Electrical 5 1 Mechanic
SELECT * 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
typeid lower upper Name 1 1 2 Buyers 2 3 3 Sellers 3 4 4 Referee 4 1 1 Electrical 5 2 2 Mechanic
SELECT * 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.
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.)
What you need is a basic parent-child relationship:
Category (ID: int, ParentID: nullable int, Name: nvarchar(1000))
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