Re: exclusive m:m relationship

From: <quanunique_at_my-deja.com>
Date: 2000/01/26
Message-ID: <86nga4$nva$1_at_nnrp1.deja.com>#1/1

One other choice would be partition the schedule table horizontally, say, ScheduleEmp for employee job, and ScheduleContract for contract job.

 CREATE TABLE ScheduleEmp
(job_nbr INTEGER NOT NULL,

  job_type CHAR(1) NOT NULL,
  emp_nbr INTEGER NOT NULL

           REFERENCES Employees(emp_nbr)
           ON DELETE CASCADE

 PRIMARY KEY(job_nbr, emp_nbr),
 CHECK (jobnbr not in SELECT job_nbr FROM ScheduleContract) );

CREATE TABLE ScheduleContract
(job_nbr INTEGER NOT NULL,

  job_type CHAR(1) NOT NULL,
  contr_nbr INTEGER NOT NULL

            REFERENCES Contractors(contr_nbr)
            ON DELETE CASCADE,

  PRIMARY KEY(job_nbr, contr_nbr),
 CHECK (jobnbr not in SELECT job_nbr FROM ScheduleEmp) );

The advantage is simpler constraint specification, compared to choice 2. The disadvantage, however, is two more CHECK constaints than choice 1.

Joins will also be more efficient between jobs and workers.

Quan.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 26 2000 - 00:00:00 CET