Related link: http://jan.kneschke.de/projects/mysql/#3
I’ve seen two other ways of storing and retrieving hierarchical data structures into relational tables. One of them is using multiple queries to retrieve children, recursing in the application. Another way is to add metadata to each row to allow value comparisons when retrieving children.
The problem with the first approach is that it requires multiple trips to the database. The problem with the second approach is the bookkeeping required to modify the metadata when you add a child to the left of other children. (Think of inserting into a b-tree, for example. I suspect there’s an algorithmic improvement lurking somewhere in the choice of numbers used in the metadata so as to reduce the need to rebalance frequently.)
The technique Jan demonstrated defines two stored procedures and calls one recursively to handle fetching children of the current node. That’s one SQL query, which is nice, and it avoids the need to insert and adjust metadata on table writes.
That seems like a nice solution to me.
What do you think?