Re: Modelling hierarchy in the relational database

Paul Brown wrote:

PB> I used Joe's scheme and the one you describe here for years. In
PB> fact, my first effort at this used the tree scheme from the Celko
PB> book (which is kind of cool). But this scheme 


('1.0')
/     |    \
/       |      \
/         |        \
/           |          \
('1.1')        ('1.2')       ('1.3')
/      \          /   \           |
/       |        /      \          |

PB>works better for most things.

Such as? Insertion speed is the only one obvious to me.

In article <7e67a7b3.0108182015.79a49dfe_at_posting.google.com>, --CELKO-- says...
>
>To show a tree as nested sets, replace the nodes with ovals, then nest
>subordinate ovals inside each other. The root will be the largest
>oval and will contain every other node. The leaf nodes will be the
>innermost ovals with nothing else inside them and the nesting will
>show the hierarchical relationship. The rgt and lft columns (I cannot
>use the reserved words LEFT and RIGHT in SQL) are what shows the
>nesting.
>
>If that mental model does not work, then imagine a little worm
>crawling anti-clockwise along the tree. Every time he gets to the
>left or right side of a node, he numbers it. The worm stops when he
>gets all the way around the tree and back to the top.
>

Overall, it looks like a physical structure exposed in the relational database.

More intuitive interpretation would be noticing that any ordered values in Joe's approach would do, not necessarily integers. In case of dense ordered set of values we don't even have to shift values whenever inserting new nodes. How about dates? Let's rename

lft INTEGER ,
rgt INTEGER

into

hired DATE,
fired DATE

and require that a subordinate employee always have the [hired, fired) interval range covered by that of his manager. Therefore, whenever we fire a manager, we need to [at least] temporarily fire his employees as well -- same amount of work as in case of integers. Received on Sun Aug 19 2001 - 21:09:52 CEST