[thelist] Database Schema Suggestions

Gee Starr geestarr at geedev.com
Mon May 3 18:28:02 CDT 2004

Burhan Khalid sez:
> I can't seem to understand all this left right notion in those tree
> layouts.  I understand how they are trying to number the system, but I
> don't understand how to figure out the tree for a decendant.

There are resources scattered throughout the web that include recipes for
sundry selection and insertion scenarios using the nested set model.
Consult your favorite search oracle with the term "nested set joe celko"
(J. Celko is pretty much the alpha proseletyte of the nested set model.)

I've been hand-rolling a few CMS's using the nested set model for the past
year, and have some PHP tools I can share if you go that route --
including a rather rube-goldbergian function that will take a nested set
result set and display it as XHTML-valid list. (Been meaning to clean up
the code and send it into the commons creative for a while, but there's
always some new feature to fuss with.)

In my experience, nested sets are best when (a) you're doing more
significantly more selecting than inserting, and/or (b) the nodes are not
the end data units but instead are categories in which you are placing
other items. And even (a) is not an issue if your hiearchy is
comparatively static and your main goal is adding new items to the
categories. Being able to grab an entire tree based on either the parent
or the child with one select statement still makes me giddy.

Meanwhile, if you add a simple adjancency list to the nested set columns
(i.e., if you track the node's parent id as well as the left and right
data in each record) you can use those to recreate the hiearchical tree
should your lefts and rights get corrupted due to transaction error or
porous logic.

I should note that I am a decided amateur in the database realm, so some
of the deeper structural problems may be out of my grasp.

//. gee of geedev dot com

More information about the thelist mailing list