Joe, I haven't been able to work out an efficient way to use your nested sets structure on dynamic hierarchies. On relatively static data, it works great, but I have trouble with it as soon as I need to move levels around, or expand the hierarchy, or anything else other than adding to the "right side" of the hierarchy.

For example, in a managers to employees hierarchy, if a department is moved to be under a different manager, the nested sets model pretty much forces a complete rebuild of the whole hierarchy, so far as I can tell. A standard hierarchy table (ID and ParentID), allows the same update by changing a single value in a single row.

I could be missing something on this (probably am), but I just can't get my head around using the nested sets for data that requires any sort of frequent updates.

I've tested it, and it does work really, really well for selects. But it adds so much complexity and so many referential integrity issues to updates and inserts, that I just can't see how to use it.

Can you point me to something that helps in that regard?

Or is it strictly meant for static/nearly static data?