Trees in MySQL 5.0

		Print
		Email weblog link
		Blog this

chromatic
Apr. 19, 2005 08:48 AM
Permalink

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.

Comments on this weblog

Showing messages 1 through 1 of 1.

• As a programmer, I like it
  2005-04-19 09:04:16  John W. Adams | [View]
  
  
  As an administrator, I'd want to compare its performance to that of making multiple trips.

---

Showing messages 1 through 1 of 1.

Return to weblogs.oreilly.com.