Re: Tree Structure & Triggers.

From: Payson (payson_b_at_hotmail.com)
Date: 06/03/04 

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
original basic.

I'll have to think about this some more.

Again, thanks for the thoughts.

Payson

jcelko212@earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0406021725.701496c7@posting.google.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"
>
> http://www.amazon.com/exec/obidos/tg/detail/-/1558609202/qid=1080772873/sr=1-1/ref=sr_1_1/102-7683601-6345721?v=glance&s=books#product-details
>
> 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) );
>
> OrgChart
> 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.

Relevant Pages

  • Re: Help with ROW_NUMBER and recursive query
    ... Well, that's a nice picture, but how do you update the values of lft & rgt when there are n millions of rows in the table? ... Let us define a simple OrgChart table like ... An employee and all their Supervisors, no matter how deep the tree. ...
    (microsoft.public.sqlserver.programming)
  • Re: Tree Structure & Triggers.
    ... Keeping the lft and rgt synchronized in a dynamic ... and rgt stay within the "parent" boundaries if a new slot is added. ... > define a simple OrgChart table like this. ...
    (microsoft.public.sqlserver.programming)
  • Re: How to normalise this scenario
    ... define a simple OrgChart table like this. ... lft INTEGER NOT NULL UNIQUE CHECK, ... rgt INTEGER NOT NULL UNIQUE CHECK, ... note that the tree structure ...
    (comp.databases.theory)
  • Re: Generating a tree structure from data
    ... The nodes and they tree ... define a simple OrgChart table like this. ... lft INTEGER NOT NULL UNIQUE CHECK, ... rgt INTEGER NOT NULL UNIQUE CHECK, ...
    (comp.databases.ms-sqlserver)
  • Re: Access 2000 Recursive Query Help
    ... > As you know, or observed, a nested set has two fields, lft and rgt, those ... > the rgt value is... ... > To get all the parent of a node is similar. ... >> Query 1 will simply give me a indented bill of material. ...
    (microsoft.public.access.queries)