Classic case of I want a shopping cart w/ categories that can be n levels deep. That is, a category can have unlimited subcategories. I'd like the SQL that brings back all the categories under a top-level category to be quick (ie: preferrably a single query) because I plan on using mod_rewrite and putting the category names in the URL.
There are two ways of structuring this in the database, an adjacency list and nested set.
However, I'd really like to hear from people's experiences using nested set. I've used the simple adjacency list, and my own hybrid version:
create table category (
category_id int unsigned not null auto_increment primary key,
category_name varchar(127) not null,
constraint unique index(category_name),
category_sort_order smallint unsigned not null default 10,
category_parent_id int unsigned default null,
index(category_parent_id),
category_depth int unsigned not null default 0 comment 'how many children this category has',
category_parent_depth int unsigned not null default 0 comment 'how many parents this category has'
) type=InnoDB;
The idea with this hybrid is that you can run one query to bring back the top-most category; examine its 'depth', then build another query to bring back the entire path from top to deepest sub-category because the 'depth' tells you how many of those crazy UNION statements to chain together as you build the query with PHP.
And if you want to play around this is some sql to set up the test data:
insert into category set category_name = 'top 1', category_depth = 3;
insert into category set category_name = 'top 2', category_depth = 1;
insert into category set category_name = 'top 3';
insert into category set category_name = 'child of top 1', category_parent_id = 1, category_depth = 2, category_parent_depth = 1;
insert into category set category_name = 'child of top 2', category_parent_id = 2, category_parent_depth = 1;
insert into category set category_name = 'child of child of top 1', category_parent_id = 4, category_depth = 1, category_parent_depth = 2;
insert into category set category_name = 'child of child of child of top 1',
category_parent_id = 6, category_depth = 0, category_parent_depth =3;
insert into category set category_name = 'sibling of child of top 1',
category_parent_id = 1, category_depth = 2, category_parent_depth =1;
and some querries...
reading from the top down:
select category_id, category_parent_id, category_name from category where category_id = 1
UNION
select category_id, category_parent_id, category_name from category where category_parent_id = 1
UNION
select category_id, category_parent_id, category_name from category where category_parent_id in
(select category_id from category where category_parent_id = 1)
UNION
select category_id, category_parent_id, category_name from category where category_parent_id in
(select category_id from category where category_parent_id in
(select category_id from category where category_parent_id = 1))
and from bottom up:
select category_id, category_name from category where category_id = 7
UNION
select category_id, category_name from category where category_parent_id in (
select category_parent_id from category where category_id =
( select category_parent_id from category where category_id = 7))
UNION
select category_id, category_name from category where category_parent_id in (
select category_parent_id from category where category_id =
( select category_parent_id from category where category_id =
( select category_parent_id from category where category_id = 7)))
UNION
-- notice the difference in this last statment
select category_id, category_name from category where category_id = (
select category_parent_id from category where category_id =
( select category_parent_id from category where category_id =
( select category_parent_id from category where category_id = 7)))
If I use nested set, do I always need a top-level set that spans all the data? The example in the link http://dev.mysql.com/tech-resources/articles/hierarchical-data.html uses a single top-level category. What happens if I have say 4 different top level categories? Seems like it doesn't work.
Thanks.
