SQL Server Performance

Best Practices for Recursive Parent/Child Queries

Discussion in 'T-SQL Performance Tuning for Developers' started by baked, Mar 20, 2003.

  1. baked New Member

    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
  2. Negative New Member

    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.
  3. baked New Member

    Thank you for the reply and link. This is good information!

Share This Page