Re: Foreign key pointing to multiple tables

>> Each Job has some details in its table, it also has a type. These
are A, B, C, D, E and F. If it is type A then it needs an additional 10 columns, B 13, C 30 and so on. <,

So these jobs are all logically different. That means they all get their own tables. This is relational thinking, not OO. Imagine a stack of jobs tickets sitting in a basket and a clerk with rubber stamps at a desk. Each form has an id number with a check digit or syntax rule to validate it since it is exposed to users. It also has a check box for the job type (a job has one and only one type). You stamp the date and time the ticket is issued:

CREATE TABLE Tickets
(job_ticket INTEGER NOT NULL PRIMARY KEY

         CHECK (<< check digit, etc.>>),  job_type CHAR(1) NOT NULL

        CHECK (job_type IN ('A', 'B', 'C', 'D', 'E', 'F', 'G')),  UNIQUE (job_ticket, job_type),
 issue_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- null is not issued yet
 ...);

The kinds of jobs have their own tables that refer back to the tickets (every job must have a ticket).

CREATE TABLE A_jobs
(job_ticket INTEGER NOT NULL

        REFERENCES Tickets(job_ticket),
 job_type CHAR(1) DEFAULT 'A' NOT NULL

       CHECK(job_type = 'A'),
 FOREIGN KEY(job_ticket, job_type)
 REFERENCES Tickets (job_ticket, job_type)   ON DELETE CASCADE
  ON UPDATE CASCADE,
 ...);

CREATE TABLE B_jobs
(job_ticket INTEGER NOT NULL

        REFERENCES Tickets(job_ticket),
 job_type CHAR(1) DEFAULT 'B' NOT NULL

       CHECK(job_type = 'B'),
 FOREIGN KEY(job_ticket, job_type)
 REFERENCES Tickets (job_ticket, job_type)   ON DELETE CASCADE
  ON UPDATE CASCADE,
 ...);

etc.

The overlapping uniqueness constraints prevent (job_ticket, job_type) = (123,'A') and (123, 'B') in the schema. Received on Sat Sep 20 2003 - 21:54:33 CEST