Re: Tree Structure & Triggers.
From: Payson (payson_b_at_hotmail.com)
Date: 3 Jun 2004 14:39:39 -0700
Interesting approach. Thanks for posting it.
After thinking about it for a while, I keep coming back to the
maintenance issue. Keeping the lft and rgt synchronized in a dynamic
organization might be a little tricky. If, for example, "Fred" has a
position created under him, his lft and rgt would need to change - the
new guy ("George") would need to be (10,11), Fred would become (9,
12), Chuck becomes (4,13) and Albert becomes (1,14). Triggers, maybe.
Thinking out loud here... The same holds true for insuring the lft
and rgt stay within the "parent" boundaries if a new slot is added. I
guess one could leave some space in between - number by tens or
somethng, but that seems kludgy - kinda like programming in the
I'll have to think about this some more.
Again, thanks for the thoughts.
firstname.lastname@example.org (--CELKO--) wrote in message news:<email@example.com>...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. Right now what you gave us is in violation of
> ISO-11179 naming rules.
> >> I have a table in my database that basically represents a tree
> structure, <<
> Here is the link on Amazon.com for my new book on "Trees & Hierarchies
> in SQL"
> The model you are using is the adjacency list model and it has all
> kinds of problems, since is based on a pointer-chain view of the world
> and not a set-oriented model.
> Next, quit using BIT datatypes. They are a low-level, proprietary
> nightmare that we used to use in tape files and punch cards.
> Think about a nested set model, instead.
> Since SQL is a set oriented language, this is a better model than the
> usual adjacency list approach you see in most text books. Let us
> define a simple OrgChart table like this.
> CREATE TABLE OrgChart
> (emp CHAR(10) NOT NULL PRIMARY KEY,
> lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
> rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
> CONSTRAINT order_okay CHECK (lft < rgt) );
> emp lft rgt
> 'Albert' 1 12
> 'Bert' 2 3
> 'Chuck' 4 11
> 'Donna' 5 6
> 'Eddie' 7 8
> 'Fred' 9 10
> The organizational chart would look like this as a directed graph:
> Albert (1, 12)
> / \
> / \
> Bert (2, 3) Chuck (4, 11)
> / | \
> / | \
> / | \
> / | \
> Donna (5, 6) Eddie (7, 8) Fred (9, 10)
> >> I have a trigger which I was hoping would enable me to check to see
> if any
> children of the updated parent, are inheriting the Parents 'Import'
> (bit column) value. If so, I wanted them to update to have the same
> value in
> their 'Import' column as the Parent. <<
> You keep thinking in terms of procedures. Add a GROUP BY and
> aggregate functions to these basic queries and you have hierarchical
> reports. For example, the total salaries which each employee controls:
> SELECT O2.emp, SUM(S1.salary)
> FROM OrgChart AS O1, OrgChart AS O2,
> Salaries AS S1
> WHERE O1.lft BETWEEN O2.lft AND O2.rgt
> AND O1.emp = S1.emp
> GROUP BY O2.emp;
> You will need to re-design the importation codes to use some aggregate
> function. Then use your version of the above query in a single UPDATE
> on the entire table.