I have a single-table with the following (relative) structure:
foo_id, parent_foo_id, foo_name
I would like to build an (effectively) infinite-depth recursive array with these entities, and consequently output them into a tree menu. Nodes with no "parent_foo_id" would be considered at the top of the heirerchy, while all successive nodes would appear as children of their parent.
What is the best method for approaching this type of data design?
I asked a similar question a while back. My question specifically had to do with SQLite, but since SQLite syntax tends to be a least common denominator subset of most other SQL flavors, it should work for you, too. You can feel free to ignore all the iPhone stuff since it's pretty irrelevant.
I hope this at least helps get you started.
Also, see this for a problem (and its solution) I ran into during my experiments with different implementation styles.
Storing hierarchical data in a relational database has as far as i'm concerned always been troublesome. Recursive methods (or adjacency methods) always suffer from performance problems sooner or later, other methods are harder to implement.
I've had the most success with the nested set method which looks at managing hierarchical data from a totally different perspective. It's hard to implement and maintain on your own though. Luckily the Doctrine ORM has a behavior to manage tree data using this algorithm thus i'd suggest looking at the theory behind this tree traversal method and then maybe take a look at the Doctrine code to get a hang of implementing this technique.