php / Mysql best tree structure

Question

I have to build a tree that will contain about 300 nodes inside it. The tree has no depth limitations. So it can have 3 or 15 levels. Each node can have an unlimited number of children.

The priority is to get a complete tree / subtree the faster as possible, but I also need to add nodes or move nodes sometimes but not that often.

I want to know the best way to store the tree in the database and the best way to retrieve the data, if possible, in php.

Answer 1

You can use a Nested Set Model as it yields very efficient queries. Check out Managing Hierarchical Data in MySQL and read the section called Nested Set Model.

If you're using an ORM like Doctrine, it includes nested set capabilities.

It can be difficult for some to grasp the nested set concepts of left and right. I have found that using those numbers as an analogy for the line numbers of open/close tags in an XML document, folks find it easier to grasp.

For instance, take the data example from the MySQL link above:

+-------------+----------------------+-----+-----+
| category_id | name                 | lft | rgt |
+-------------+----------------------+-----+-----+
|           1 | ELECTRONICS          |   1 |  20 |
|           2 | TELEVISIONS          |   2 |   9 |
|           3 | TUBE                 |   3 |   4 |
|           4 | LCD                  |   5 |   6 |
|           5 | PLASMA               |   7 |   8 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |
|           7 | MP3 PLAYERS          |  11 |  14 |
|           8 | FLASH                |  12 |  13 |
|           9 | CD PLAYERS           |  15 |  16 |
|          10 | 2 WAY RADIOS         |  17 |  18 |
+-------------+----------------------+-----+-----+

If you take the lft, rgt fields and use them as line numbers for an XML document, you get:

1. <electronics>
2.    <televisions>
3.        <tube>
4.        </tube>
5.        <lcd>
6.        </lcd>
7.        <plasma>  
8.        </plasma> 
9.     </televisions>
10.    <portable electronics>
11.        <mp3 players>
12.            <flash>
13.            </flash>
14.        </mp3 players>
15.        <cd players>
16.        </cd players>
17.        <2 way radios>
18.        </2 way radios>
19.    </portable electronics>
20. </electronics>

Seeing it this way can make it much easier for some to visualize the resulting nested set hierarchy. It also makes it clearer why this approach improves efficiency as it makes it possible to select entire nodes without the need for multiple queries or joins.