Qual è il modo migliore per gestire un "sindacato" in un database relazionale?

Se X ha una relazione 1: M con A, B e C (essendo X nel lato M) ma vuoi che l'FK indichi A e quello che punta a B si escluda a vicenda, devi aggiungere un assegno vincolo alla tabella.

ALTER TABLE A ADD CONSTRAINT check_001 CHECK 
    ((a_id IS NULL and b_id IS NOT NULL) OR 
    (a_id IS NOT NULL and b_id IS NULL));

La soluzione normalizzata standard "dal libro" consiste nell'aggiungere le tabelle di collegamento X_A, X_B, X_C ciascuna con due chiavi esterne. Ad esempio, in X_A:

• a_id che fa riferimento a A
• x_id che fa riferimento a X, che rappresenta una relazione 1: 1!

Tuttavia, se vale davvero la pena di creare queste tabelle aggiuntive, o se si può vivere con una soluzione meno normalizzata con a_id, b_id, c_id tutto in X, dipende molto dal caso.

La semplice risposta è di capovolgere la dipendenza dalla chiave.

La tabella X dovrebbe avere una singola chiave primaria, forse denominata xid , che è unica nell'intera tabella. Non sono necessarie chiavi esterne.

Ogni riga in A , B , C e D dovrebbe avere anche una colonna denominata xid . Se la relazione è veramente 1: 1, allora xid potrebbe anche servire come chiave primaria per A , B , C e D pure.

Per unirti a loro userai

SELECT *
FROM   X
LEFT JOIN A on A.xid = X.xid
LEFT JOIN B on B.xid = X.xid
LEFT JOIN C on C.xid = X.xid
LEFT JOIN D on D.xid = X.xid

Quanto sopra è solo un sottotipo / supertype implementazione del database , niente di particolarmente insolito a riguardo.

Se sei molto preoccupato per quei join extra che causano I / O non necessari, e solo uno tra {A, B, C, D} sarà mai valido per una data X, potresti anche aggiungere un attributo aggiuntivo per aiutarti query trova la tabella giusta. Basta aggiungere una colonna aggiuntiva a X , forse denominata xtype , contenente un singolo carattere ("A", "B", "C" o "D"), quindi scrivere i join in questo modo:

SELECT *
FROM   X
LEFT JOIN A on A.xid = X.xid AND x.xtype = 'A'
LEFT JOIN B on B.xid = X.xid AND x.xtype = 'B'
LEFT JOIN C on C.xid = X.xid AND x.xtype = 'C'
LEFT JOIN D on D.xid = X.xid AND x.xtype = 'D' 

Questo finirà per essere leggermente più efficiente, perché il motore di query non si preoccuperà nemmeno di provare a fare un join in A, B, C o D a seconda di xtype . Lo svantaggio in questo caso è che devi mantenere xtype aggiornato e non esiste alcun vincolo implicito che ti impedisca di impostare un xid che punta a A ma a xtype che punta a B . Dovresti implementare questo vincolo da solo.