Hierarchy

Hi to All!

Is there a perfect method to implement a hierarchy structure with different types as a table in Sql server 2005? Currently I am thinking of this way:

[Node | ParentId | ParentType | ChildId | ChildType]

But there is this nagging little voice saying it can be better

Cheers!

Nele

? Can you share some additional information? What do the "types" represent? Most of the time when I see the need for typing, it means that a table for that type should be created, and through key relationships this forms a natural hierarchy. In your system can the type hierarchy change? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Eburon@.discussions.microsoft.com> wrote in message news:f4c76476-0e56-4c53-939b-ffb7750ab228@.discussions.microsoft.com... Hi to All! Is there a perfect method to implement a hierarchy structure with different types as a table in Sql server 2005? Currently I am thinking of this way: [Node | ParentId | ParentType | ChildId | ChildType] But there is this nagging little voice saying it can be better Cheers! Nele|||

[same author as Eburon]

It is indeed a natural hierarchy. I assume that the type hierarchy cannot change, but a type can be absent. More in detail there are three types, t1, t2, t3:

t1 is always the root [level 1],|||? I would model this as three tables. T2 and T3 would both have self-referencing keys (i.e., "parent" keys)... CREATE TABLE T1 ( T1Id INT NOT NULL UNIQUE, ... //other attributes, including natural PK ) GO CREATE TABLE T2 ( T1id INT NULL REFERENCES T1 (T1id), T2id INT NOT NULL UNIQUE, parentT2id INT NULL REFERENCES T2 (T2id), ...//other attributes, PK, etc CONSTRAINT CheckIDsNOTNULL CHECK (COALESCE(T1id, T2id) IS NOT NULL) ) GO CREATE TABLE T3 ( T2id INT NULL REFERENCES T3 (T13id), T3id INT NOT NULL UNIQUE, parentT3id INT NULL REFERENCES T2 (T3id), ...//other attributes, PK, etc CONSTRAINT CheckIDsNOTNULL CHECK (COALESCE(T2id, T3id) IS NOT NULL) ) GO -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Nele@.discussions.microsoft..com> wrote in message news:27029b28-63cc-419f-8340-5be221c78682@.discussions.microsoft.com... [same author as Eburon] It is indeed a natural hierarchy. I assume that the type hierarchy cannot change, but a type can be absent. More in detail there are three types, t1, t2, t3: t1 is always the root [level 1], t2 may be added to go lower down the hierarchy [level 1.1 -> level 1 ... 1], and t3 may be added as a child [level 1 ... 1.1]. Is that sufficient additional information?|||I had not thought of that solution. Thank you!