I have to store a tree in a database, so what is the best way to do this? Show the method you use and name its pros and cons. (I am using SQL Server 2005)


did you bother to search before you posted?

Eric H.
I've found several techniques but I want to find out which are worth use and to have a kind of comparison
This isn't an answer.
Bill the Lizard
I didn't downvote you but I think you are missing the point, Eric. The idea is to collect as much knowledge on SO as possible so it becomes a central repository of programming advice. If we only allowed questions that couldn't be answered somewhere else, this would be a pretty barren place.
Mark Brittingham
+2  A: 

Well, the easiest way would be for a record to have a ParentID column so that it knows which record is its parent. This is a pretty standard practice. For example, an online store might have a hierarchy of product categories. Each category will have a ParentID. Example: The "Jeans" category in an clothing database might have "Pants" as the parent category. It's a bit harder if you want a record to indicate which are its children, unless you restrict the number of children. If you want a binary tree, you could have LeftChildID and RightChildID columns. If you allow any number of children, you could have a Children column with IDs delimited by commas (such as 1,4,72,19) but that will make querying rather hard. If your database allows for array types in columns, you can probably use an array instead of a delimited string, which would be easy to query - but I'm not sure if MS SQL Server supports that or not.

Other than that, it depends on what kind of data you are modelling, and also what sort of operations you plan to do with this tree.

+4  A: 

try this: Hierarchies (trees) in SQL Server 2005

+1  A:

I found that a very helpful guide.

+1  A: 

There are two general approaches

  1. In each record, Store the Id of the parent in a nullable column (the root of the tree has no parent)
  2. Use Joe Celko's nested set model technique explained here and (thanks to comment from @onedaywhen), also in what is the original source here

    Pros and Cons ?? !! you're kidding, right ?!

Charles Bretana
Celko attributes the nested set model to Michael Kamfonas, though Celko seems to have coined the phrase and certainly polished and popularized the technique.
+1 - I just used Celko's article a few weeks ago and it was very helpful.
Mark Brittingham

I have done this in the past by storing data as xml in SQL.

+1  A: 

I found the discussion in the SQL Anti-patterns very helpfull, as it also focuses on the drawbacks of every implementation.

Also, The slides 48-77 in this presentation reiterate that analisys.

Bottom line, there is no such thing as a generic tree, and no silver bullet for SQL trees. You'll have to ask yourself about the data, how and how much will they be selected, modified, will branches be moved around, etc, and based on those answers implement a suitable solution.