Обсуждение: More schema design advice requested

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

On Mon, Oct 13, 2008 at 9:29 AM, Matthew Wilson <matt@tplus1.com> wrote:

> 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.

I don't have the book in front of me at the moment, but I remember
this exact problem and a unique solution using a schema redesign
around skill sets that would return results very quickly.  The method
described in the query was referred to as "full disjunction".

http://www.elsevier.com/wps/find/bookdescription.librarians/710075/description#description
sell the problem: 17: EMPLOYMENT AGENCY PUZZLE


Sorry that I can't be of more help than this.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Mon, Oct 13, 2008 at 1:11 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:
> On Mon, Oct 13, 2008 at 9:29 AM, Matthew Wilson <matt@tplus1.com> wrote:
> I don't have the book in front of me at the moment, but I remember
> this exact problem and a unique solution using a schema redesign
> around skill sets that would return results very quickly.  The method
> described in the query was referred to as "full disjunction".

Perhaps you can try:

http://pgfoundry.org/projects/fulldisjunction/

--
Jonah H. Harris, Senior DBA
myYearbook.com

On Mon, Oct 13, 2008 at 04:29:45PM +0000, Matthew Wilson wrote:
> 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.
>
> I want to find all jobs that employee #2 is qualified for

I think you want to use an "outer join".  Maybe something like:

  SELECT r.job_id, bool_and(q.employee_id IS NOT NULL) AS is_qualified
  FROM job_requirement r
    LEFT JOIN employee_qualification q
               ON q.requirement_id = r.requirement_id AND
              q.employee_id    = 2
  GROUP BY r.job_id;

If you want to extend this to more than one employee you'd need to do
something like:

  SELECT e.employee_id, r.job_id
  FROM employees e, job_requirement r
    LEFT JOIN employee_qualification q
               ON q.requirement_id = r.requirement_id AND
                  q.employee_id    = e.employee_id
  WHERE e.employee_id IN (2,3,4)
  GROUP BY e.employee_id, r.job_id
  HAVING bool_and(q.employee_id IS NOT NULL);

I.e. get the cross product of all employees and jobs, match them up to
what they're qualified for.  Moving the "is_qualified" expression down
into the HAVING clause causes the query to only return jobs for which
the employee is fully qualified for.


  Sam