Menu
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)
500
The standard method of storing hierarchical data is simple parent-child relationship. Each record in the database includes a —parent id—, and a recursive query through the records build the children, siblings, and levels of the tree.
You could use adjacency lists (your current idea), nested sets, or even (with appropriate database support) arrays of node ids to represent the path from the root. Which representation you choose depends on what you need to do to your data. @Kim, why array? If the graph is a tree, each node has at most one parent.
An alternative method to allow us to retrieve the entire tree is to have an extra column that identifies the tree to which each node belongs. This could be the ID of the root node, or some other identifier for the entire tree. This means that our table gains an extra column, so we're storing more data.
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.
178
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
