Nested Set Model

• Chad Crawford

  SSChampion

  Points: 11610

  More actions

  May 23, 2008 at 11:54 am

  #207738

  Does anyone have experience with managing hierarchical data using a nested set model? How do you like it? I need to implement a tree relationship structure, but I'm leaning more towards a model like what the HierarchyID uses in SQL 2008. I'd appreciate your thoughts.

  Nested sets (e.g. http://dev.mysql.com/tech-resources/articles/hierarchical-data.html):

  Every node as a left and right numeric "edge" value. All children of this node have L/R edge values between the L/R edge value of the parent. For example, if "Bob" has L-Edge=5 and R-Edge=14, then "Frank" is a child node because he has L-Edge=6 and R-Edge=7. Finding parent/child lists is done using a BETWEEN.

  Hierarchial Model:

  Every node has a hierarchy value consisting of it's parent's hierarchy value plus a unique value among all it's siblings.

  for example:

  Bob: /00/05/A7/H2/

  Frank: /00/05/A7/H2/22

  Frank's unique value among Bob's children is "22". "/00/05/A7/H2/" indicates Bob is Frank's parent, and "/00/05/A7/" would be the value specifying Bob's parent, and so forth. Finding parent/child relationships is done using a LIKE. This seems to be much easier model when you have frequent insert/updates (which we well), since the nested set model will require the whole tree to be updated for each change. It will also be relatively easy to move to the HierarchyID when we upgrade to 2K8, if we decide to change the datatype.

  Thanks!

  Chad