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)

8 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?

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 🙂

Leave a Reply to James Crowley 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.