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!