Обсуждение: 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