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