the approach you seem to already be using of having a column for the parent is pretty standard. obviously it's not optimal if you have very deep trees. the other typical solution is to use an object-relational database like postgresql, but once you start using those features, there goes portability.

if you're really careful, you might be able to hack something clever. if your rows each have a unique key, you could maybe add a 'branch', or 'path' column that you populate with a string containing the keys for each layer going down to the row seperated by commas or something. eg, if row 123 is a child of 56 which is a child of 34 which is a child of 12, you could store '12/34/56' in the branch column for row 123. then, after you select, you just split it in perl and off you go. of course, this could turn into a nightmare to maintain and you should avoid doing it unless the performance hit of multiple selects is really the bottleneck.

this might also be a good time to think about alternatives to trees.

anders pearson