homepage Welcome to WebmasterWorld Guest from 54.198.42.105
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

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




msg:4595985
 6:20 am on Jul 24, 2013 (gmt 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:

college
trainer
department
supervisor (all four have different columns)

practice
exam

service
criteria


Complications:

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


Solution?

Service [service_criteria] Criteria
Trainer [trainer_practice] Practice
...
...
etc

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!

 

brotherhood of LAN




msg:4596077
 2:58 pm on Jul 24, 2013 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved