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)
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 ... ... 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;
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.