CODEWITHSUNDEEP
databasedatabase-designforeign-keysrelational-databasedatabase-schema
Ivan Rubinson
Ivan Rubinson

Reputation: 3361

How to model exclusive relation in relational database?

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:

  1. B holds a foreign key to A (B.A_id), C holds a foreign key to A (C.A_id). I can't think of a way to constrain the exclusivity of the association.
  2. A holds a foreign key to B (A.B_id), and another foreign key to C (A.C_id). We can constrain the exclusivity via CHECK (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.
  3. A holds an unconstrained column (A.foreign_id), which may be either Bs or Cs key, and A holds another column that signifies the type (A.foreign_type = either B or C). 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 the A.foreign_type column)

Which scales best? Are there pros/cons to discuss that weren't listed? Are there any other alternatives to consider?

Upvotes: 0

Views: 40

Answers (1)

nik0x1
nik0x1

Reputation: 1461

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)
);

Upvotes: 0

Related Questions