January 28, 2003
Implementing a tree in SQL
Recently on the agile databases mailing list someone raised the spectre of implementing a hierarchy in a relational database. Someone then almost reflexively talked about Joe Celkos set based approach to implementing a hierarchy.
Now I like a parent-child table but I like to think I am open to new ideas. But this one does rather hurt my brain. I can see how easy it is to traverse an existing tree, but it looks very hard to populate the left and right values. As far as I can tell each time you add or remove a node from the tree you have to recalculate the left right values for each node in the tree. Which looks rather hard to me.
Still, I am going to do my best to get to the bottom of it and try and implement it in one of my databases.
Posted by Andy Todd at January 28, 2003 05:21 PM