Tree structures in ASP.NET and SQL Server

I’ve just posted my latest article on Developer Fusion – Tree structures in ASP.NET and SQL Server, which takes a look at how you can store tree structures in SQL Server – and then doing something useful with them such as creating a web-directory like interface such as the one below.

I’d really appreciate any comments you might have …! :,,)

(Also – if you’ve got any articles you’d like to see on Developer Fusion, please do drop me a line)

8 thoughts on “Tree structures in ASP.NET and SQL Server”

  1. Great article on a tricky issue with databases. One technique I”ve used several times successfully is Modified Preorder Tree Traversal (http://www.sitepoint.com/article/hierarchical-data-database/). It”s very similar to this, but uses Left and Right IDs instead of a lineage path to determine parent/child nodes.

    It”s more difficult to understand, but you don”t have the issue of deeply nested nodes with a lineage that”s too long for the column.

  2. I”m with rick. The Adjacency List (as you”ve described) is a very poor model for representing heirarchy in a relational database. Just look at the redudant data your storing (Lineage and Depth).

    Checkout Joe Celko”s models. They”re a bit more complicated, but well worth using. Just have the SPROCs do the legwork, and you”re golden.

    Now go rewrite your article 😉

  3. Cheers for the comments guys. I”ve been taking a look at the Modified Preorder Tree Traversal method… but am I correct in thinking that you”re basically going to have to rebuild the entire tree each time you make a modification?

  4. You are correct, and it does increase INSERT time a little, but the SELECTS can be very fast (without the recursive overhead). You have to weigh your options, but the second model, if done correctly, does offer many.

  5. The method I”ve demonstrated using those extra columns avoids the overhead for SELECT statements too – at the cost of some additional storage – but I”ll see if I can figure out some triggers to automatically maintain the modified preorder values… Thanks 🙂

  6. I”m actually trying to implement this now. I”m running into an issue with recursive cursors. If you get this figured out, let us know. I”ll do the same.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.