Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Modelling hierarchy in the relational database

Re: Modelling hierarchy in the relational database

From: Philip Lijnzaad <lijnzaad_at_ebi.ac.uk>
Date: 05 Sep 2001 00:11:29 +0100
Message-ID: <u7n14atuu6.fsf@mozart.ebi.ac.uk>

> 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
Received on Tue Sep 04 2001 - 18:11:29 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US