More schema design advice requested

Поиск
Список
Период
Сортировка
От Matthew Wilson
Тема More schema design advice requested
Дата
Msg-id slrngf6trp.ia3.matt@sprout.tplus1.com
обсуждение исходный текст
Ответы Re: More schema design advice requested
Re: More schema design advice requested
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Merlin Moncure"
Дата:
Сообщение: Re: PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
Следующее
От: Reg Me Please
Дата:
Сообщение: NATURAL JOINs