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?
You should use either nested sets or parent-child models.
Parent-child:
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 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
SELECT *
FROM group_types
JOIN mytable
ON group BETWEEN lower AND upper
WHERE typeid = 1
will select all buyers in any database.
See this answer for more detail.
Nested sets is more easy to query, but it's harder to update and harder to build a tree structure.
A similar question was recently asked.
Check out this article at SitePoint:
Make a categories table with the following fields:
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
What you need is a basic parent-child relationship:
Category (ID: int, ParentID: nullable int, Name: nvarchar(1000))
From the example in your question it looks like you'd want it to be possible for a given category to have multiple parents (e.g., "MIT Videos -> Video 1 Programming" as well as "Video -> Video 1 Programming"), in which case simply adding a ParentID column would not be sufficient.
I would recommend creating two tables: a simple Categories table with CategoryID and CategoryName columns, and a separate CategoryRelationships table with ParentCategoryID and ChildCategoryID columns. This way you can specify as many parent-child relationships as you want for any particular category. It would even be possible using this model to have a dual relationship where two categories are each other's parent and child simultaneously. (Off the top of my head, I can't think of a great use for this scenario, but at least it illustrates how flexible the model is.)
Blockquote Quassnoi said :
You should use either nested sets or parent-child models.
Blockquote
I used to implement both of them. What I could say is :
use the nested set architecture if your categories table doesn't change often because on a select clause it's fast and with only one request you can get the whole branch of the hierarchy for a given entry but on a insert or update clause it takes more time than a parent child model to update the left and right (or lower and upper in the example below) fields.
Another point, quite trivial I must admit, but... It's very difficult to change the hierarchy by hand directly in the database (It could happen during the dev level). So, be sure to implement first an interface to play with the nested set (changing parent node, move a branch node, deleting a node or the whole branch etc..)
Here are two links on the sub ject :
www.sitepoint.com/article/hierarchical-data-database/
dev.mysql.com/tech-resources/articles/hierarchical-data.html
I'm new here, so I can't create hyperlinks
Last thing, I didn't try it, but I read somewhere that you can have more than one tree in a nested set table, I mean several roots.
Bye
Fred
A better way to store the parent_id of the table is to have it nested within the ID e.g
100000 Programming 110000 C Language 111000 Video 1 Programming 111100 C Language 111110 Stanford Video
etc..so all you need it a script to process the ID such that the first digit represents the top level category and so on as you go deeper down the hierarchy