Best Practices for Recursive Parent/Child Queries | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Best Practices for Recursive Parent/Child Queries

Hi, I am designing a database that will have a lot of recursive relationships that can be best described as: Container/Containee – One record may reference several sub-records or several container records. Original/Derivative – One record is the "original" but we need to track many derivitaves of the original. In this case, each record (whether it is a container/containee/original/derivative) will still need to be treated as a seperate record of equal "standing" in the database. Is this a common problem and if so are there any recommended "Best Practices"? Any recommendations or suggestions for the architecture of this would be appreciated. Thank you, Paul
I would recommend looking at this article: http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci537290,00.html The article is by Joe Celko, and in my opinion shows the best way to represent trees and hierarchies in SQL databases that I have seen. I have used the adjacency list model before, and while adjacency lists are easy to run ad hoc queries on, the nested set model is much better if you want a more reliable database structure. In the article Joe even provides sample queries to do some of the most common tasks using a hierarchy. A small tip from my experience using hierarchies:
In databases that I have used the nested set model (or the adjacency list model for that matter) I have found that seperating the hierarchy data into a seperate table is usually a good idea. If you need to add or remove a hierarchy that relates to the same data it is much easier.
Thank you for the reply and link. This is good information!
]]>