From: | Matthew Wilson <matt(at)tplus1(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | More schema design advice requested |
Date: | 2008-10-13 16:29:45 |
Message-ID: | slrngf6trp.ia3.matt@sprout.tplus1.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Reg Me Please | 2008-10-13 16:52:57 | NATURAL JOINs |
Previous Message | Merlin Moncure | 2008-10-13 16:08:49 | Re: PL/pgSQL stored procedure returning multiple result sets (SELECTs)? |