Fractional CTO | Sustainability, Climate & Impact
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)
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 😉
Yes, take a look at Joe Celko”s excellent work on this — his most recent book is all about this interesting topic.
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 🙂
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.
I haven”t come across that before – what”s happening exactly?
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 James Crowley Cancel reply