I track employee qualifications in one table and I track job
requirements in another table. A job requires zero-to-many
qualifications, and for an employee to be qualified for that job, the
employee must have ALL the requirements.
For example, In my job requirements table, I record that a nurse must
have a TB test and a nursing license like this:
(nurse job ID, TB test ID)
(nurse job ID, nursing license ID)
Then I record employee qualifications for each employee like this:
(Alice's ID, TB test ID)
(Alice's ID, nursing license ID)
(Bob's ID, TB test ID)
Alice is qualified to work as a nurse. Bob is halfway there, but he
still needs to get the nursing license.
When I want to find all jobs that employee #2 is qualified for, I do
something like this:
select job_id, bool_and(is_qualified)
from
(
select job_requirement.job_id, requirement_id, requirement_id in
(
select requirement_id from employee_qualification
where employee_id = 2
) as is_subscribed
from job_requirement) as x group by job_id;
This works, but man, it makes me dizzy.
Any advice? I wonder if this is a sign of a bad design, or maybe if
there's some nicer SQL techniques I could use.
Thanks in advance!
Matt
