Storing Hierarchical Data in a Database

[Anonymous]

Excellent article which is a great primer on storing hierarchical data.
However, I ran into a snag using the mptta. Recursion may not be required to query the tree, but it is for building or modifying the tree. I wrote about a different method. I'll post a link as soon as Sitepoint lets me. This method uses the path enumeration model, with a twist. Enjoy.

[Obsessive]

Also, your solution using the LEFT and RIGHT is simply turning a hierarchical model into a binary try, which will have severe limitations when building a complex tree, such as when a child has multiple parents, and those parents have other multiple children.

Just like to point out, that Andrew is talking about graphs, while the tutorial was about hierarchical data model, which is a binary tree. Though, there are other kind of models.

Anyway, great article A while ago I used adjacency model, but now i use b-tree like. It's true that it's harder to understand, but it's worth it, due to the performance gained when retrieving the list.

[Jose Hernandez]

The line

INSERT INTO tree SET lft=6, rgt=7, title='Strawberry';

is missing the parent field. Otherwhise, the record has no parent:

INSERT INTO tree SET lft=6, rgt=7, title='Strawberry', parent='Red';

[jlhill]

As for the issue raised about designs where the child has multiple parents, I believe (it seems to work for me) to modify the rebuild_tree function like:

rebuild_tree($parent, $left, $mparent)
. . .
$right = rebuild_tree($row['title'], $right, $parentid);
. . .

mysql_query('UPDATE tree SET lft='.$left.', rgt='.$right.' WHERE title="'.$parent.'" && mparent="'.$mparent.'";');

Difficult to fully explain in a comment box, but this seems to work for me.

[jim]

I hate to say it, but this method seems to be an outlandish hack. Wouldn't it be much simpler to build a trigger generated 'flattened' table (a table with one record for each node in which the node is a member of)?

[viaria]

good stuff but not emough for us who is not familiar,
what if i make a cms and customer ads a sub menu then all the left and right numbers have to change, how can we calculate and update other fields,
this way i can use xml or html too,
thanks and please respond,

[phazei]

I like that rebuild tree function. Yeah it's a bit expensive, but for something like a category list, even if there's a couple hundred, it's only a one time expense. Really makes the rest a breeze. A custom sort order could also be incorporated as simply as: "SELECT title FROM tree WHERE parent="'.$parent.'" ORDER BY 'sort_order' ASC"

One question though. If not going about doing it the lazy err easy way, adding isn't that difficult, but what about moving nodes? I'd like to see a solution that's elegant that can switch Yellow and Red. I suppose it would need to look at red lft and yellow rgt and just reorder between. But that could still be lots of work in a large structure. Any good solutions out there?

[Blacksonic]

Great article!!!
the rebuild function was the most useful, cuz other functions are also readable elsewhere
in some days i'll make the node moving functions...i'm really impressed with nested set...getting the tree with nested set is 3 times quicker even with this little tree, than the normal way

[geniegate]

I had a problem like this awhile ago.

Here's my solution:
http://geniegate.com/art/pdf/tree-st...l-database.pdf

It was kind of nice to be able to do this:

SELECT 
  * 
FROM 
  directory 
WHERE 
  lookup_directory_ancestor_path(id) LIKE 'Blah/%';

And not clutter the program up with a bunch of tree handling stuff.

I was going to go with flat files at first, but decided I wanted relational after thinking about some of the other aspects (integrity checks).

You can also create database-side indexes, but in practice, I haven't needed them. (I just created an index, then dropped it, to see if it'd actually work.)

[tudoroiuj]

how can i find the level?

[r937]

how can i find the level?

which level?

please show your table design

[Kalon]

how can i find the level?

My apologies if this has already been suggested earlier, but this thread is too long for me to go back looking through all the posts.

To store hierarchial data in a database I would recommend using The Nested Set Model. This link also has some very useful sql scripts on extracting hierarchy information from the database.

I have posted some sql scripts on how to move hierarchial nodes.

[bergmp]

Nice tutorial, let me ask you something, how can I create an automated hierarchical tree of 5 or more, for example:

Electronics
dvd
eye cleaner
movies: horror
movies: drama
movies: comedy
movies: thriller
cd player
camera
recorder
tv

I'm triying to do something like this with your sample, without no success.
Thanks for your help!

[said_fox]

Is there any third approach for dealing with hierarchical data?

[Kalon]

Nice tutorial, let me ask you something, how can I create an automated hierarchical tree of 5 or more, for example:

Electronics
dvd
eye cleaner
movies: horror
movies: drama
movies: comedy
movies: thriller
cd player
camera
recorder
tv

I'm triying to do something like this with your sample, without no success.
Thanks for your help!

I'm not sure what you mean by "automated" hierarchal tree.

What I normally do is start off with a root node in the database. I then give the user a form to add nodes with as many sub-nodes as they like. The form prompts for a new node name and a reference node to add the new node to. When the form is submitted I then run sql scripts as in or similar to the tutorial to add the node.

The sql scripts add the node name and give it appropriate left and right values according to where it is being added in the tree. The nodes to the right of the added node then have their left and right values adjusted accordingly.

[doddsey_65]

sorry to revive an old topic but couldnt find help elsewhere. I am using the preorder tree traversal for my forum and i need to display the subforums under the forum names as a list like many ohter forums do. unfortunatly i dont know how to go about it. anyone have any ideas?