[eluser]vinofilus[/eluser]
This application will track people who hold 1 or 2 jobs in a business, and each job has multiple training requirements, and each training course has several specific rules. My tables (simplified for this example):
Regular Tables:
Staff: id, fname, lname
Jobs: id, job_name (ie cook, maintenance, registrar)
Course: id, course (union intro, food handling, sterilization)
Rules: id, rule (require when hired, refresh every year, every 3 years)
Link Tables:
Staff_Jobs: staff_id, jobs_id
Jobs_Course: jobs_id, course_id
Course_Rules: course_id, rules_id
Some staff members hold down 2 jobs, thus the staff_jobs link table.
Each job requires up to several different courses, thus the jobs_course link table.
Each course may have several applicable rules, thus the course_rules link table.
Let’s say I want to create a report (or temporary table) showing all the information available, which might look somelthing like this:
Fname lname | Job | Course | Rules
Bob Smith | Cook | Union Intro | When Hired
Bob Smith | Cook | Food Handling | When Hired
Bob Smith | Cook | Food Handling | Every year
Bob Smith | Registrar | Union Intro | When Hired
Anne Bedier | Maintenance | Union Intro | When Hired
Anne Bedier | Maintenance | Sterilization | When Hired
Anne Bedier | Maintenance | Sterilization | Every 3 years
This will be done in codeigniter, and it seems to me I have 2 options:
1) A fancy set of joins, possibly needing full outer or even a cross join?
2) Foreach loops in the model, like:
For each staff, look up the jobs they do
For each job, look up related courses
For each course, get related rules
Write out a line of fname, lname, job, course, rule to temp table (or save to an array)
Once I have that one big table or array created, I can compare that to a log file of what has already been completed and quickly generate all the reports and graphs I need, such as who is behind on taking required courses, which courses are most missed, which job has the best record, and so on.
Option 2 seems easier to understand logically, though I’m not yet sure how I’d do that in the model, specifically how I'd pass all that data to an array properly.
Option 1 may be the more appropriate database way to do it, but complex joins make my brain hurt.
I’m not sure which would be more efficient.
I’m looking for suggestions, recommendations, helpful comments, or even code examples to get me unstuck.
Thanks,
Les…
