More schema design advice requested

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

Responses

Browse pgsql-general by date

  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)?