Trees in MySQL 5.0

   Print.Print
Email.Email weblog link
Blog this.Blog this

chromatic
Apr. 19, 2005 08:48 AM
Permalink

Atom feed for this author. RSS 1.0 feed for this author. RSS 2.0 feed for this author.

URL: 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.

chromatic manages Onyx Neon Press, an independent publisher.

Return to weblogs.oreilly.com.



Weblog authors are solely responsible for the content and accuracy of their weblogs, including opinions they express, and O'Reilly Media, Inc., disclaims any and all liabililty for that content, its accuracy, and opinions it may contain.

Creative Commons License This work is licensed under a Creative Commons License.