alternatives to adjacency model? (hierarchical data)

11 Aug 2005 7:36 PM

n_o_s_p_a__m

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

11 Aug 2005 7:48 PM

David Browne

<n_o_s_p_a***@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/microsoft.public.sqlserver.programming/msg/47c0072cb60b7151?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

11 Aug 2005 7:55 PM

ML

Your model lacks circular reference prevention. Consider something like the
solution suggested in this thread: http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.programming&mid=d5b9f5d8-4790-4088-bf18-14de5119891d&sloc=en-us

ML

11 Aug 2005 7:55 PM

Alejandro Mesa

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/Articles/Index.cfm?ArticleID=8826&DisplayTab=Article

Manipulating Hierarchies with UDFs http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=16123&DisplayTab=Article

Trees and Hierarchies in SQL http://www.amazon.com/exec/obidos/tg/detail/-/1558609202/qid=1123789976/sr=1-1/ref=sr_1_1/104-1160706-5950324?v=glance&s=books

AMB

Show quote

"n_o_s_p_a***@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
>
>

11 Aug 2005 11:01 PM

--CELKO--

Go out and buy a copy of TREES & HIERARCHIES IN SQL. It will save you
a lot of trouble and pay my mortgage.