Best way to represt n/ depth tree for use in PHP (MySQL / XML /?)?

I don't know that you actually need 4 tables for this If you have a single table that tracks the parent_id and a level you can have infinite levels outcome id, parent_id, level, name You can use recursion to track through the tree for any particular element (you don't actually need level, but it can be easier to query with it) The alternative is nested sets. In this case you would still merge to a single table, but use the set stuff to track levels Which one to use depends on your application Read-intensive: nested sets Write-intensive: parent tree thingy This is because with nested sets you can retrieve the entire tree with a single query but at the cost of reordering the entire tree every time you insert a new node When you just track the parent_id, you can move or delete nodes individually PS: I vote no to XML. You have the same recursive issues, plus the overhead of parsing the data as well as either storing it in the db or on the filesystem (which will cause concurrency issues).

I don't know that you actually need 4 tables for this. If you have a single table that tracks the parent_id and a level you can have infinite levels. Outcome id, parent_id, level, name You can use recursion to track through the tree for any particular element (you don't actually need level, but it can be easier to query with it).

The alternative is nested sets. In this case you would still merge to a single table, but use the set stuff to track levels. Which one to use depends on your application.

Read-intensive: nested sets Write-intensive: parent tree thingy This is because with nested sets you can retrieve the entire tree with a single query but at the cost of reordering the entire tree every time you insert a new node. When you just track the parent_id, you can move or delete nodes individually. PS: I vote no to XML.

You have the same recursive issues, plus the overhead of parsing the data as well as either storing it in the db or on the filesystem (which will cause concurrency issues).

Thanks for the advice, sometimes you just need a fresh opinion to point out the blatantly obvious. Cheers :) – Danny Dec 4 '08 at 10:56.

I agree with the other poster - nested sets is the way to go I think. See here: dev.mysql.com/tech-resources/articles/hi... It explains the theory and compares it to what you are already using - which is a twist on adjacency really. It shows +/- of them all, and should help you reach a decision based on all of the subtleties of your project.

Another thing I've seen (in CakePHP's tree behaviour) is actually to use both at once. Sure its not great performance wise, but under this model, you insert/remove things just as you would with adjacency, and then there is a method to run to rebuild the left/right edge values to allow you to do the selects in a nested sets fashion. Result is you can insert/delete much more easily.

book.cakephp.org/view/91/Tree.

I have come across a CI class that allows for a combination Nested set and adjacency model similar to the cakephp version you have linked to which I may give a try. I will post back with the results. – Danny Dec 4 '08 at 10:56.

There is another way to handle trees in a database that is maybe not as "smart" than nested sets and other patterns described here, but that is really efficient and easy : instead of storing the level (or depth) of an item, you can store the full path in the tree, like this : A B C D E would be stored like this: item | parent | path ---------------------------- A | NULL | A B | A | A--B C | A | A--C D | C | A--C--D E | A | A--E then you can easyly get: (pure SQL) all direct children of an item with a where parent = '' clause (pure SQL) all direct and indirect children with a where path LIKE 'PARENT--%' clause (PHP) the depth of the node (count(explode('--',$path)) those features are good enough in most situations, and quite performant, even with several sublevels, as long as you create the good indices (PK, index on parent, index on path). For sure, this solution is demanding when deleting/moving nodes to update pathes... I hope this helps!

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.