Re: Bill of materials / Groups in Groups

From: <joe_celko_at_my-deja.com>
Date: 2000/01/24
Message-ID: <86id54$5v$1_at_nnrp1.deja.com>#1/1

Let's see if I can get this right

 CREATE TABLE OrgChart
 (emp CHAR(10) PRIMARY KEY,
  lft INTEGER NOT NULL UNIQUE CHECK (lft > 0), -- key   rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1), -- key   CONSTRAINT subset_definition
  CHECK (lft < rgt), -- domain constraint   CONSTRAINT subordination -- domain constraint   CHECK (NOT EXISTS

         (SELECT *
            FROM OrgChart AS C1
           WHERE C1.emp <> OrgChart.emp
             AND C1.lft BETWEEN OrgChart.lft AND OrgChart.rgt
             AND Orgchart.rgt BETWEEN C1.lft AND C1.rgt)),

  CONSTRAINT compound_key UNIQUE (lft, rgt) -- key   );

I think that gives us the minimal constriants we want. Well, the compound key constraint looks redundant, but this does give us all of the keys in the table explicitly.

In practice, you would also add a minimal numbering constraint and enforce it with a trigger that would close up gaps for neastness and the ability to detect leaf nodes with the predicate (rgt - lft = 1) and to get counts of the subtree rooted at each node.

The domains of the (lft,rgt) keys reference the table itself. You cannot have duplicates in either member of the pair and they cannot overlap with an existing pair.

>> The nested set model works ONLY if you write complex stored
procedures to do all of your updates, and you allow the procedures to temporarily violate the constraints of the relation. This is fine from a practical standpoint, and can be done effectively with today's DBMSs. However, it does NOT satisfy the definition of DKNF. <<

No, I can get by with just an intentional definition of the domain of valid (lft,rgt) pairs in a compound key. But if I do not close up the gaps, I lose a lot of power that is handy for doing queries based on simple algebra on the (lft,rgt) pairs.

>> I still don't believe that there's any definition of normalization
according to which the nested set model is normalized while the adjacency model is not. <<

Again, delete Chuck and you lose subordination in the adjacency list model, but not in the nested sets. Update Chuck in in the adjacency list model and he has be changed in his other roles, but not in the nested sets.

--CELKO-- Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Jan 24 2000 - 00:00:00 CET