Re: exclusive m:m relationship

From: <joe_celko_at_my-deja.com>
Date: 2000/01/25
Message-ID: <86lcae$6f2$1_at_nnrp1.deja.com>#1/1

>> A project is done by a number of workers, doing a particular job,
which could be either employed of contracted. Contractors and employees are stored in different tables. Jobs in projects could also be done by more than one worker.

 I have come up with these two options: .. <<

The # is not part of Standard SQL, so you should avoid using it. It would also help if you posted real DDL instead of writign table descriptions in a personal pseudo-code. I am guessing that your tables look like this:

CREATE TABLE Schedules

(job_nbr INTEGER NOT NULL,
 job_type CHAR(1) NOT NULL,
 emp_nbr INTEGER NOT NULL
          REFERENCES Employees(emp_nbr)
          ON DELETE CASCADE
 contr_nbr INTEGER NOT NULL
           REFERENCES Contractors(contr_nbr)
           ON DELETE CASCADE,

 PRIMARY KEY(job_nbr, emp_nbr, contr_nbr));

>> Each record refers to either employee or contractor exclusively. <<

Sorry, but a PRIMARY KEY has to be made of NOT NULL columns. This one is impossible as I understand it, because either emp_nbr or contr_nbr would be NULL.

of the two choices you gave, I would go with this:

CREATE TABLE Schedules
(job_nbr INTEGER NOT NULL,
 staff_type CHAR(1) NOT NULL CHECK (staff_type IN ('E', 'C')),  staff_nbr INTEGER NOT NULL

           CHECK((staff_nbr IN (SELECT emp_nbr FROM Employees)
                  AND staff_type = 'E')
              OR (staff_nbr IN (SELECT contr_nbr FROM Contractors),
                  AND staff_type = 'C'))

 PRIMARY KEY(job_nbr, staff_nbr));

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