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, PaulI 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!
]]>