Categories
ASP.NET Software Engineering

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)

9 replies on “Tree structures in ASP.NET and SQL Server”

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.

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 😉

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?

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.

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 🙂

This is an old article but I’ve found it and the comments useful. While the Adjacency List might have posed problems back in 2005, databases have evolved to include special features to make this work better (Connect By, With Clauses). I’ve written a guide to designing hierarchical data in SQL here, and I recommend the Adjacency List in most situations.
Here’s the guide: https://www.databasestar.com/hierarchical-data-sql/
Thanks!

Leave a Reply to rick Cancel 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.