Re: Treelike data: adjacency list or nested sets?

I almost always use "adjacency lists" along with another table for the actual categorization of the info (in your case, published articles). I find the "nested sets" design limiting unless you have complete foreknowledge of your tree/categories when initially populating the table. If you have to modify the tree/categories later on, it can get hairy. Doug At 02:40 PM 4/20/01 +0200, Tomás GarcíY+0=a Ferrari wrote: >Hello, > >I'm developing a system (with PHP-MySQL) in which I will have different >articles published. Any given article can correspond to a category (or >sub-category (or sub-sub-category)). Something like this: > >Cat 1 > Sub 1.1 > Sub-Sub 1.1.1 > Sub-Sub 1.1.2 > Sub 1.2 > Sub 1.3 >Cat 2 > Sub 2.1 > Sub 2.2 > Sub-Sub 2.2.1 > Sub-Sub 2.2.2 >[etc] > >As per my research on this topic of publishing 'treelike information', there >are two posibilities: > >a) Adjacency list > > id | parent | label > 1 0 Cat 1 > 2 1 Sub 1.1 > 3 2 Sub 1.1.1 > [so on...] > >b) Nested sets (as explained in the book JOE CELKO'S SQL FOR SMARTIES >(Morgan-Kaufmann, 1999, second edition), that I don't have...) > > label | lft | rgt > Cat 1 1 12 > Sub 1.1 2 7 > Sub 1.1.1 3 4 > Sub 1.1.2 5 6 > Sub 1.2 8 9 > Sub 1.3 10 11 > [so on...] > > >Anybody is doing something like this that can share experience with us? I >can not find a clue yet if one system is better than the other for what I >need. I realized that the 'Nested Sets' system have a very good approach and >is very easy to get all the sub (and sub-sub) categories on a given category >(any record where lft is between lft and rgt: > SELECT e1.* > FROM example as e1, example as e2 > WHERE e1.lft BETWEEN e2.lft AND e2.rgt > AND e2.label = 'Cat 1'; >) > >But I'm having problems trying to find -as example- which are all the >Categories (in the other system are all the records where parent = 0)... > >Any ideas? > >Regards, >Tomas Garcia Ferrari > >Bigital >http://bigital.com >