Storing Hierarchical Data in a Database

I came across these two articles while researching ways to speedup wayfinder and found them quite interesting. I’m not sure this is something we would want to do for modx but thought I would put it out there. It would allow us to use one query to get the whole doc tree instead of using recursion... I have never used this method in an actual production environment, but it looks kind of neat and quick

Trees in SQL:
http://www.intelligententerprise.com/001020/celko1_1.jhtml

Storing Hierarchical Data in a Database:
http://www.sitepoint.com/article/hierarchical-data-database

Has anyone used this before? I would be interested to hear someones experience with it...

Never used it before either but it looks promising.

Very nice!

It’s very good for retrievals but it appears that there would be some performance issues when updating large trees.

Thanks for sharing.

I would think for the purpose of document heirarchy, that a one-time hit on update vs. an every time hit on menu creation would be worth it. Then again, isn’t this partially or wholly what Jason’s docmap API addition does that will be in 0.9.5?

This is exactly what documentMap is and getParentIds() gets your path upstream, while getChildIds() builds a set of children. Both have been in the code since 0.9.2 and I’ve added new height/depth parameters respectively in SVN for even more usage possibilities.
The adjacency list model, which we already employ for the document tree (i.e. parent), is the best approach for modeling our trees in the data, and we should avoid at all costs the nested sets model. It is very difficult to maintain in many respects. It’s much easier to handle the problem of parents being removed by allowing administration functions which allow a new parent to be substituted, or all children to be removed in a cascading fashion.