Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

Joe Celko's "Nested Set Model of Hierarchies"?

6:20 am on Jul 24, 2013 (gmt 0)

New User

5+ Year Member

joined:Sept 18, 2011
posts: 12
votes: 0

The most challenging part of my database example would make the rest a tad easier to figure/configure for me.

I've read pretty much everything, including Joe Celko but it's like that last dime hiding from the accountant...for me, at least.

Here's my challenge...

one college - many trainers

one college - many classes
one college - many tests
one college - many service_entries
one college - many classes_service_criteria (each has individual weight)
one college - many test_service_criteria (each has individual weight)

one trainer - many classes
one trainer - many tests
one trainer - many service_entries
one trainer - many classes_service_criteria (each have individual weight)
one trainer - many test_service_criteria (each has individual weight)

one department - many supervisors

one department - many tests
one department - many service_entries
one department - many test_service_criteria (each has individual weight)

one supervisor - many tests
one supervisor - many service_entries
one supervisor - many test_service_criteria (each has individual weight)

From the above, I know I'll need a table each for:

supervisor (all four have different columns)




1) All parties are being scored on a criteria weight basis (per row in the database)
2) Each party has their own "criteria set" on basis of service extended in training or exam, or both


Service [service_criteria] Criteria
Trainer [trainer_practice] Practice

Even if I do many-to-many joins, I still feel I'll end up doing many-to-many [JOINED TO] many-to-many...if that makes sense. Clearly adjacency is already, but will become an issue bigger than I imagine.

Could Joe Celko's "Nested Set Model of Hierarchies" be the answer?

Frankly, the queries I'll have to cope with will be very daunting, if this would have to be the case :(

I can't ever see myself fathom a norm or getting used to... (Joe Celko quoted example)

SELECT Mgrs.member AS boss, Workers.member AS worker
FROM Orgchart AS Mgrs, Orgchart AS Workers
WHERE Workers.lft BETWEEN Mgrs.lft AND Mgrs.rgt
AND Workers.rgt BETWEEN Mgrs.lft AND Mgrs.rgt;

ANY insight most greatly appreciated!
2:58 pm on July 24, 2013 (gmt 0)

Senior Member from GB 

WebmasterWorld Senior Member brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 30, 2002
votes: 22

JOINs are fine, as long as you can retrieve the rows you want and no more, preferably by querying by primary key.

I've had a play with the nested set model, it's a clever idea for a database. I'd say it's beneficial to you if:
- You don't need to continually update the lft and rgt values or at least not the lft and rgt values near the top of the tree
- The table is small if you do need to regularly update

This is because you have to update all the rows to the rgt of the row you need to update, and also for inserts.

I think it's great for large, deep, relatively static tree structures, e.g. a category structure for a directory.