Re: Modelling hierarchy in the relational database

> What happens when a new position is created? Do I have to go through
> and adjust the left and right data for each existing person?

yes, unfortunately that's the case (and a bit of a drawback of the trick). Celko's book gives recipes of how to do this I believe, but I don't remember them as being particularly simple.

The problem with the nestd set representation is that each node relates to the whole tree, not to its localality (parents and children).

While its certainly faster for general tree-querying, it is cumbersome for highly dynamic trees, as well as for queries involving nodes separated by exactly N levels (typically N=1).

In practice, many people denormalize (*) and add parent_id to the table definitions.

Cheers,

                                                                      Philip

(*) I won't be drawn into the debate over wether both, either or neither of

    the nested-set cq. classical representation are (more) normalized. Well,     ok, a bit then: both are normalized.

-- 
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