Hierarchy

Hierarchy

• themicks

  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 Machanic
  Pro SQL Server 2005, available now
  http://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

  
  

• Darren Dmello

  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 Machanic
  Pro SQL Server 2005, available now
  http://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

  
  

• omerkaan

  [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

  
  

• -- sam --

  I had not thought of that solution. Thank you!
  
• Hierarchy