Re: Tree structure in Relational DB design

>> Wrong. The foreign key constraint declared in the create table will
prevent
any anomaly [updating 'Chuck' in multiple places].... The fact that a foreign key constraint, rather than a more complex general constraint, suffices to enforce integrity is a good indication that the design IS normalized. <<

I agree that you can use declarative referential actions or triggers to cascade the update within the table. But those are actions; the fact that you have to take an action means that there is a problem that cannot be solved by the relationships alone.

What is involved here is a very complex integrity constraint -- a recursive self-reference with cascading actions. That is probably why programmers who use the adjacency list model don't add this constraint like they should.

>> The redundancy is necessary for the foreign key and causes no
update
anomalies. <<

Glad to see that you admit it is a redundancy <g> ...

>> One man's entity (employment_contract, for instance) is another
man's
relationship (employs / is employed by). The distinction is arbitrary and
essentially meaningless. <<

Having a contract and doing the work are two different things. The inability to tell the difference between an entity and a relationship had made some really bad data designs; Date's orthogonality principle gets a real beating at times. How exactly, would I perform "Groceries" in a database that models a supermarket?

>> Huh? A unique parent is the defining characteristic of a hierarchy.
General
inheritance graphs are DAGs, I believe. <<

No, that's a tree, not a hierarchy. A hierarchy is a special case of a tree with some extra rules -- inheritance. If you shoot your Sargent, you still have to take orders from the General because the chain of command is a hierarchy. If I dam a river, the flow downstream stops because a river system is a tree. Am I being clear enough?

>> Costin's point has nothing to do with whether the lft and rgt are
scalars. The lft and rgt do not directly identify either an ancestor or a decendant. <<

The (lft,rgt) pairs identify the entire set of ancestors and decendants via the BETWEEN predicate. The adjacency list model, however, cannot take two arbitrary rows and determine if one is an ancestor or a decendant of the other. You have to navigate the links via a cursor or use iteration with UNIONs and joins -- procedural code. Received on Tue Jun 11 2002 - 19:27:29 CEST