I had a similar database construct that I built a tree structure around. I won't attempt to write your code, but I'll explain what I did and maybe it will help.

Each level of the tree was an array reference. The array members were hash references representing the individual nodes. Those nodes had optional references to other array references (children nodes), etc ...

To implement this you have two functions: One to create the tree, and one to traverse it. The creation function algorithm is:

• Accept one optional argument: A parent node (hash reference)
• Get nodes for the parent (or root nodes if no parent given) from the database
• If the DB hands back any nodes ...
• Create an array reference
• For each DB node returned ...
• Create a hash reference representing the node. Keys are name, ID, etc.
• Push the created hash (node) reference onto the array created above
• Call yourself recursively with the node reference as an argument
• If a parent was given set a "children" key in it to the created array reference
• Return the created array reference (or undef if no nodes)

Traversal is implemented as a function taking an array reference of nodes, a depth, and a function (CODE) reference to call for each node. The called node function takes two arguments: A node and a depth. The traversal function algorithm looks like this:

• Accept three arguments: An array reference of nodes, a depth, and a CODE reference to a function to call per node
• For each node in the given array reference ...
• Call the node processing function with the node and the given depth
• If the node has a "children" array reference ...
• Call yourself recursively with the "children" array reference, depth+1, and the node processing CODE reference

You build the tree by calling the creation function with no arguments. Then you print the header and call the traversal function with the tree (array reference handed back by creation), a depth of zero, and a reference to a sub that does your printing. Your printing sub takes a node (hash reference) and a depth. You indent based on depth and print the name that's in the hash reference representing the node.

Once I got this far I optimized by loading all nodes with one DB call and building the tree as a secondary step. But I was able to do that by virtue of having another set of data that defined the relationships (the node "links"). So your mileage may vary there.

I was proud of this one. I built it fast and none of our Java programmers ever built anything like it. That wasn't so much a Java versus Perl thing (which I don't argue) -- it was more a function of me having done dozens of similar things in other languages and being very comfortable with the model. But it showed that Perl was powerful and could handle complex data structures. For a long time that Perl package was the key to people getting a fast, hierarchical view of the data. It's still used heavily today, several years later. One key to its longevity IMO is the abstracted traversal that takes a CODE reference to do the work. I've built dozens of functions to do different things as each node is visited. Easy to do once the core creation and traversal is implemented.