Re: Creating a Hierarchical Structure

> How would you, or what is the commonly accepted way, of creating a database
> which represents a hierarchical category structure ala Yahoo!?

The commonly accepted way is excatly the one you outline. The drawback is you can't pull out complete subtrees with one statement (unless you have something like Oracle's CONNECT BY PRIOR). Joe Celko regularly posts a different solution to your problem; I think i saw his post this week! Search groups.google.com for "celko hierarchy".

Note that not everybody agrees with Celko's claim that the 'common way' is not normalized. The problem with is approach is that it's difficult to select the direct parents or direct children of a given node. In practice, you might be best off with having both LFT and RGT and PARENT_ID, giving you the best of both worlds, at the expense of denormalisation.

> What I have done until now is to create a table called categories. Each
> field has a categoryID, a parentID and the category's name. The parentID is
> "0" for all top level categories, and all sub-categories inherit a parentID
> containing the categoryID of the category above it. So for instance:
 

> CategoryID ParentID Name
> 1 0 Shopping
> 2 1 Retail
> 3 1 Business to Business
> 4 2 Clothing

> I was talking to some guy yesterday who said the standard what of creating
> such a system in a directory was to have a table which contains your root
> categories and then a new table for every level of sub-category.

This sounds positively insane to me, in the general case. For very fixed hierarchies it may work though (although I don't see how you would portably determine which table to select from/join to during a query).

                                                                      Philip

-- 
Real programs don't eat cache (Malay)
-----------------------------------------------------------------------------
Philip Lijnzaad, lijnzaad_at_ebi.ac.uk \ European Bioinformatics Institute,rm A2-08
+44 (0)1223 49 4639                 / Wellcome Trust Genome Campus, Hinxton
+44 (0)1223 49 4468 (fax)           \ Cambridgeshire CB10 1SD,  GREAT BRITAIN