Let there be 3 tables: A, B, C.
A row in A may be associated to a row in B, or it may be associated to a row in C, but it must not be associated to both B and C.
In other words, the association is an exclusive-or, one-to-one.
Here are some proposed alternatives:
Bholds a foreign key toA(B.A_id),Cholds a foreign key toA(C.A_id). I can’t think of a way to constrain the exclusivity of the association.Aholds a foreign key toB(A.B_id), and another foreign key toC(A.C_id). We can constrain the exclusivity viaCHECK (A_id IS NULL AND B_id IS NOT NULL) OR (A_id IS NOT NULL AND B_id IS NULL). But we pay in width (memory & disk), because we don’t use half of the foreign key columns.Aholds an unconstrained column (A.foreign_id), which may be eitherBs orCs key, andAholds another column that signifies the type (A.foreign_type= eitherBorC). This satisfies both the exclusivity constraint, and minimizes width, but loses foreign-key constraint validation, and makes querying complex (who I join with is predicated on theA.foreign_typecolumn)
Which scales best? Are there pros/cons to discuss that weren’t listed? Are there any other alternatives to consider?
0
Alternative 1. The problem with this approach is that the A may contain something different from what is in B or C. If this situation is acceptable for you, then the following design (conceptually) could be a solution to your exclusivity problem:
CREATE TABLE A
(
type SMALLINT,
id INTEGER,
PRIMARY KEY (type, id)
);
CREATE TABLE B
(
type SMALLINT,
id INTEGER,
PRIMARY KEY (type, id),
CHECK ( type = 1 ),
FOREIGN KEY (type, id) REFERENCES A (type, id)
);
CREATE TABLE C
(
type SMALLINT,
id INTEGER,
PRIMARY KEY (type, id),
CHECK ( type = 2 ),
FOREIGN KEY (type, id) REFERENCES A (type, id)
);
Alternative 2. The problem with this approach is that B and C may contain something that A will not contain. If this is not a problem for you, then I would not worry about disk space at all (the best way to check this is to conduct an experiment).
Alternative 3. If you want to have constraints, consider making one table BC instead of two tables B and C.
CREATE TABLE BC
(
type SMALLINT,
id INTEGER,
b_field INT,
c_field INT,
PRIMARY KEY (type, id)
);
CREATE TABLE A
(
type SMALLINT,
id INTEGER,
PRIMARY KEY (type, id),
FOREIGN KEY (type, id) REFERENCES BC (type, id)
);