alternatives to adjacency model? (hierarchical data)

Hello Gurus,
I am looking for a hierarchical modeling technique other than
adjacency.
Simplicity is the goal. Updates and inserts must be reasonably easy.
For example, I'd like to see some modeling options other other this:
CREATE TABLE [Hier] (
[ID] [int] NOT NULL ,
[PARENT_ID] [int] NULL ,
CONSTRAINT [PK] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Parent] FOREIGN KEY
(
[PARENT_ID]
) REFERENCES [Hier] (
[ID]
)
)
Anyone know of any (links are often helpful)?
Thank you,
-KJ<n_o_s_p_a__m@.mail.com> wrote in message
news:1123788975.065397.6950@.g47g2000cwa.googlegroups.com...
> Hello Gurus,
> I am looking for a hierarchical modeling technique other than
> adjacency.
> Simplicity is the goal. Updates and inserts must be reasonably easy.
>
Well, there's the nested set model. But it simplifies nested searching and
complicates inserts and updates.
For instance in this example
http://groups-beta.google.com/group...b60b7151?hl=en&
This is required to insert a node
BEGIN
DECLARE right_most_sibling INTEGER;
SET right_most_sibling
= (SELECT rgt
FROM Personnel
WHERE emp = :your_boss);
UPDATE Personnel
SET lft = CASE WHEN lft > right_most_sibling
THEN lft + 2
ELSE lft END,
rgt = CASE WHEN rgt >= right_most_sibling
THEN rgt + 2
ELSE rgt END
WHERE rgt >= right_most_sibling;
INSERT INTO Personnel (emp, lft, rgt)
VALUES ('New Guy', right_most_sibling, (right_most_sibling + 1))
END;
And you probably should introduce a transaction and take a TABLOCKX on
Personnel to control concurrency.
David|||Your model lacks circular reference prevention. Consider something like the
solution suggested in this thread:
http://msdn.microsoft.com/newsgroup...891d&sloc=en-us
ML|||Trees in SQL: Nested Sets and Materialized Path
http://www.dbazine.com/oracle/or-articles/tropashko4
SQL Lessons
http://www.dbmsmag.com/9604d06.html
Maintaining Hierarchies
http://www.windowsitpro.com/Article...=glance&s=books
AMB
"n_o_s_p_a__m@.mail.com" wrote:

> Hello Gurus,
> I am looking for a hierarchical modeling technique other than
> adjacency.
> Simplicity is the goal. Updates and inserts must be reasonably easy.
> For example, I'd like to see some modeling options other other this:
> CREATE TABLE [Hier] (
> [ID] [int] NOT NULL ,
> [PARENT_ID] [int] NULL ,
> CONSTRAINT [PK] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Parent] FOREIGN KEY
> (
> [PARENT_ID]
> ) REFERENCES [Hier] (
> [ID]
> )
> )
> Anyone know of any (links are often helpful)?
> Thank you,
> -KJ
>|||Go out and buy a copy of TREES & HIERARCHIES IN SQL. It will save you
a lot of trouble and pay my mortgage.