Re: Creating a Hierarchical Structure

From: --CELKO-- <joe.celko_at_trilogy.com>
Date: 12 Sep 2001 08:44:07 -0700
Message-ID: <7e67a7b3.0109120744.25dff008@posting.google.com>

>> how would I insert new "persons" into the tree. <<

Find the parent node ('mom', a, b) under which the new node appears. Make him the rightmost node ('kid', b+1, b+2), and update all lft and rgt values greater than (b).

>> For my application each node, must have a maximum of four
sub-nodes. <<

Create the entire heirarchy and leave NULLs in the nodes until they are filled. This assumes that you want a rigid structure.
I you mean that every subtree has no more than four sub-ordinates, that gets a little weird. The size of a subtree rooted at a given node is ((rgt - lft +1)/ 2).

CREATE TABLE Tree
(node CHAR(10),
  lft INTEGER NOT NULL UNIQUE,
  rgt INTEGER NOT NULL UNIQUE,
  CHECK(lft < rgt),
  CHECK (MOD(((rgt - lft +1)/ 2), 5) = 0); Received on Wed Sep 12 2001 - 10:44:07 CDT