Vedo due potenziali problemi con il tuo progetto:
- Invertire un'associazione in bianco e nero e le relative configurazioni inserendo una chiave esterna nella parte sbagliata di tale associazione
- Disporre di due sottotipi di blocchi (immutabili e quelli con configurazioni) in un'unica tabella: "ereditarietà di una tabella"
Sembra che tu stia cercando di implementare un'associazione 1-to-N standard b / w blocks
e block_configurations
, ma per qualche motivo hai deciso di capovolgerla inserendo la chiave di configurazione in ogni blocco, mentre dovresti fare il contrario, cioè dovresti inserire block_id
in block_configurations
invece di mettere configuration_id
in blocks
:
CREATE TABLE blocks (
block_id CHAR(10),
is_immutable BIT(1) NOT NULL,
PRIMARY KEY (block_id)
);
CREATE TABLE block_configurations (
configuration_id INT(10) AUTO_INCREMENT,
block_id CHAR(10) NOT NULL,
PRIMARY KEY (configuration_id),
FOREIGN KEY block_id_fk(block_id) REFERENCES blocks(block_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
);
L'unica cosa che non viene applicata da questo schema è il fatto che i blocchi immutabili non devono avere record di configurazione associati. Puoi farlo usando i trigger, ma la soluzione migliore è usare tabelle configurable_blocks
e immutable_blocks
separate, poiché in questo caso ci sono due sottotipi di blocchi. Ci sono due modi per farlo: "ereditarietà di tabelle multiple" e "ereditarietà di tabelle di classi". Quello che ho mostrato sopra è una "ereditarietà di una tabella singola", in cui due sottotipi di blocchi sono memorizzati all'interno di una singola tabella.
Ecco un esempio con ereditarietà di più tabelle:
CREATE TABLE immutable_blocks (
block_id CHAR(10),
PRIMARY KEY (block_id)
);
CREATE TABLE configurable_blocks (
block_id CHAR(10),
PRIMARY KEY (block_id)
);
CREATE TABLE block_configurations (
configuration_id INT(10) AUTO_INCREMENT,
block_id CHAR(10) NOT NULL,
PRIMARY KEY (configuration_id),
FOREIGN KEY configurable_block_id_fk(block_id)
REFERENCES configurable_blocks(block_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
);
Questo design ha un altro difetto: se si hanno attributi comuni in due sottotipi di blocchi, è necessario duplicarli su due tabelle. Ecco dove risiede l'ereditarietà delle tabelle delle classi:
CREATE TABLE blocks (
block_id CHAR(10),
description VARCHAR(100), -- common field
PRIMARY KEY (block_id)
);
CREATE TABLE immutable_blocks (
block_id CHAR(10),
FOREIGN KEY block_id_fk(block_id) REFERENCES blocks(block_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
);
CREATE TABLE configurable_blocks (
block_id CHAR(10),
FOREIGN KEY block_id_fk(block_id) REFERENCES blocks(block_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
);
CREATE TABLE block_configurations (
... same here, still references only configurable_blocks ...
);
Questo design garantisce l'integrità referenziale e consente di separare i campi specifici comuni e sottotipo.
Lettura correlata:
-
Antipattern SQL , capitolo "Associazioni polimorfiche": le soluzioni in questo capitolo si applicano a questo caso
- SQL Antipatterns, capitolo "Entity-Attribute-Value" - descrive l'ereditarietà di tabelle single / multiple / class, in relazione al problema di avere sottotipi di qualche entità