Svantaggi dellutilizzo di una chiave esterna nullable invece di creare una tabella di intersezione

I due modelli rappresentano relazioni diverse.

Utilizzando una tabella di join , stai modellando una relazione molti-a-molti.

Usando una semplice chiave esterna, stai modellando una relazione uno-a-molti.

Lo svantaggio di un nullable straniero fondamentale è non poter modellare la relazione come molti-a-molti, se questo è ciò che stai cercando di ottenere.

In base alla tua modifica alla domanda, stai effettivamente dividendo il tavolo degli studenti in due tabelle con la stessa chiave. In genere lo vedo sui tavoli che hanno troppi campi, quindi qualcuno li divide in due per essere più gestibili (lo chiamo mettere il rossetto su un maiale).

Dividendo il tavolo dello studente, stai facendo la seconda tabella è facoltativa perché non è necessario che un record esista nella seconda tabella. Che è molto simile a un campo che non necessita di essere impostato perché può essere nullo.

Se desideri una relazione uno-a-molti, è molto meglio usare una singola tabella e consentire lID scuola essere nullo nella tabella degli studenti. Non cè motivo per evitare valori nulli nei campi, nemmeno per una chiave esterna. Ciò significa che la relazione esterna è facoltativa: gli sviluppatori e gli amministratori di database lo capiscono chiaramente e il motore di database sottostante dovrebbe certamente funzionare bene.

Se sei preoccupato per i join, non preoccuparti. Esistono semantiche ben definite per il funzionamento dei join con i campi nulli. Utilizzando una singola tabella, puoi unire due tabelle invece di tre.

Commenti

• Quindi, se sto modellando una relazione uno-a-molti (con partecipazione facoltativa sul lato: 1), dovrei utilizzare una chiave esterna nonostante possa avere valori NULL?
• @Tom sì, quello è esattamente come modellarlo. Sebbene tecnicamente possibile utilizzare una tabella di join, il modello di dati consente molti a molti, quindi avrai bisogno di trigger e logica del database per impedirlo. È meglio limitare la relazione in modo che sia impossibile aggiungere dati errati.
• Ho modificato la mia domanda.Ho creato solo student_id una chiave primaria nella tabella School_has_Student, che ha mantenuto la relazione uno-a-molti. Quali svantaggi presenta questo metodo rispetto allutilizzo di una chiave esterna?
• @Tom Ho modificato la mia risposta.

Hai scritto in un commento sopra:

il libro “Fundamentals of Database Systems” […] dice [.. .] che si consiglia di utilizzare una tabella di intersezione se sono presenti molti valori NULL nella colonna della chiave esterna (ad esempio: se il 98% dei dipendenti non gestisce un dipartimento)

Quando ci sono molti valori NULL nella colonna della chiave esterna, i tuoi programmi dovranno occuparsi di questa colonna per lo più vuota per ogni record che elaborano. La colonna probabilmente occuperà dello spazio su disco anche se nel 98% dei casi è vuoto, interrogare la relazione significa interrogare quella colonna che ti dà più traffico di rete, e se stai usando un ORM che ti genera classi dalle tue tabelle, i tuoi programmi avranno anche bisogno di più spazio sul client lato del necessario utilizzando un inters La tabella delle sezioni evita questo, ci saranno solo record di collegamento necessari dove la chiave esterna equivalente non sarebbe NULL altrimenti.

Al contrario, se non hai solo pochi valori NULL, diciamo 50% o più le relazioni non sono NULL, luso di una tabella di intersezione ti dà leffetto opposto: più spazio su disco, maggiore complessità con conseguente maggiore traffico di rete, ecc.

Quindi usare una tabella di intersezione è solo una forma di ottimizzazione, sensato solo per un caso specifico, e soprattutto oggigiorno, in cui lo spazio su disco e la memoria sono diventati più economici, molto meno frequentemente necessari. Si noti che “Fundamentals of Database Systems” è stato scritto originariamente più di 20 anni fa (ho trovato un riferimento alla seconda edizione del 1994), e immagino che quella raccomandazione fosse già presente in quel momento. Prima del 1994, lottimizzazione dello spazio era probabilmente molto più importante di oggi, poiché larchiviazione di massa era ancora più costosa e i computer e le reti erano molto più lenti di oggi.

Come nota a margine di un commento esigente: Laffermazione di cui sopra sta solo cercando di anticipare ciò che lautore di “Fundamentals of Database Systems” aveva in mente con la sua raccomandazione, immagino che stesse facendo una dichiarazione approssimativa e generale, valida per la maggior parte dei sistemi. In alcuni database ci sono altre possibili ottimizzazioni come “colonne sparse” che rendono luso di una tabella di intersezione ancora più obsoleto.

Quindi non fraintendere questa raccomandazione. Il libro non dice preferisci le tabelle di intersezione per le relazioni {0,1}:n in generale, o – come hai scritto – che questo è il “modo corretto”. Usa ottimizzazioni come questa che renderanno i tuoi programmi più complicati solo quando ne hai davvero bisogno.

Commenti

• ‘ stai assumendo molto sullimplementazione del database, soprattutto considerando che lOP non ‘ ne ha menzionato uno specifico. ‘ è più che probabile che il database sia abbastanza intelligente da usare solo una piccola quantità di spazio per colonne sparse.
• @gardenhead: cosa ti fa credere che questo è ” più che probabile “?
• Il fatto che i database abbiano esistono da decenni e sono altamente ottimizzati in quanto sono un componente critico della maggior parte delle infrastrutture.
• @gardenhead: mi sembra che tu stia facendo supposizioni ingiustificate più di me. Tuttavia, vedi la mia modifica.

Il modello concettuale sarà simile a questo, che è molto poco ortodosso per dire meno:

Il modello fisico avrà questo aspetto, il che è confuso per dire di meno (le persone penseranno che” s M: M a meno che non vedano da vicino):

Il mio suggerimento:

Se ti piace, molte colonne (FK o altro), che non si applicano alla maggior parte degli studenti, separa le tabelle in tabelle di ruolo con relazioni 1: 1. Ma non è perché sono FK, è perché le colonne non si applicano alla maggior parte delle righe.

Altrimenti , FK nullable è una parte normale di un database e le tabelle di join sono solitamente per M: M rels.

Gli usi comuni di rel 1: 1 sono per le tabelle dei ruoli con colonne che si applicano solo se lentità è di un certo tipo e lestrazione di colonne BLOB per considerazioni di prestazioni o archiviazione. Avodare valori nulli in FK non è un utilizzo comune per questo.

Oltre ad altre risposte vorrei sottolineare che un valore nullo per la chiave esterna è ambiguo. Significa:

1) La scuola dello studente (se esiste) è sconosciuta (questo è il significato standard di “null” – il valore è sconosciuto)

2) È sapere se lo studente ha una scuola e non ne ha nessuna

Se usi il significato standard di null, come rappresenteresti “lo studente non ha scuola” nel tuo modello di chiave esterna. In tal caso, probabilmente dovresti creare una voce “nessuna scuola”, con il suo ID nella tabella della scuola. (Non ideale)

Commenti

• Il libro ” Fundamentals of Database Systems ” menziona che ci sono 3 interpretazioni per NULL, può significare: 1) Valore sconosciuto. 2) Valore non disponibile o trattenuto. 3) Attributo non applicabile (penso che questa interpretazione significhi che puoi specificare un NULL per una chiave esterna).
• Questo ‘ è un elenco utile ma la semantica di null (o qualsiasi valore in realtà) è definibile dallutente.può significare qualunque cosa il designer dice che significa, non limitato a quella lista. Il problema è come distinguere significati diversi quando più di uno potrebbe essere richiesto (o anche salvato involontariamente)
• Quindi mi stai suggerendo di creare una tabella di intersezione invece di usare una chiave esterna nullable?
• @Tom Sì, credo che sia meglio in questo caso
• @BradThomas – per evitare la stessa ambiguità quando usi una tabella di intersezione, rappresenteresti il caso 2 (è noto che lo studente ha nessuna scuola) da un record nella tabella di intersezione con un NULL School_ID?

Le tabelle di database hanno questo cosa bella chiamata vincoli. Quindi è molto facile creare una tabella di intersezione che consente a solo 1 di ogni studente di apparire nella tabella ma molte scuole in quella tabella. Darti effettivamente una

Teoria è carina ma alla fine lo sei modellare il database in base alle domande che stai ponendo.

Se vuoi porre spesso domande con la domanda: “quali studenti sono nella mia scuola”, vuoi davvero interrogare lintera tabella degli studenti o una semplice tabella di intersezione.

Nei database: ottimizza per le domande che fai.

Lì è un caso duso in cui lutilizzo di una terza tabella può effettivamente avere senso. Lesempio può sembrare puramente ipotetico, ma spero che illustri bene il mio punto. Supponiamo che tu aggiunga più colonne alla tabella students e ad un certo punto decidi di imporre lunicità dei record tramite lindice composto su più colonne. È molto probabile che tu “Dovrò includere anche la colonna school_id e qui le cose iniziano a diventare disordinate. A causa del modo in cui è stato progettato SQL, inserendo diversi record identici in cui school_id è NULL sarà possibile. Ha perfettamente senso dal punto di vista tecnico, ma è controintuitivo e può portare a risultati inaspettati. Daltra parte, rafforzare lunicità tabella di intersezione è facile.

Di recente ho dovuto modellare una relazione di questo tipo “opzionale”, in cui il requisito per un vincolo di unicità era dovuto a una colonna timestamp. Lasciare la chiave esterna nullable nella tabella ha portato allimprovviso possibilità di inserire record con lo stesso timestamp (supponiamo che sia uno predefinito, impostato su record che non sono stati controllati / appr ancora) – e lunica via duscita era rimuovere la colonna nullable.

Quindi, come puoi vedere, “è un caso abbastanza specifico, e come altri hanno notato, la maggior parte delle volte” saresti perfettamente daccordo con tutti i valori NULL. Dipende molto dai requisiti specifici del tuo modello.

Oltre ai molti buoni suggerimenti già inviati, personalmente “Non sono un fan delle chiavi esterne a meno che non siano veramente necessarie. Innanzitutto cè la relazione M: M a cui stai facendo riferimento. Inoltre, chiamare una chiave esterna, e quindi inserire i dati della tabella nelle tue query, introduce maggiore complessità e dipende dimensione della tabella, prestazioni più lente. Come altri hanno detto, i campi FK nullable possono non essere supportati e possono creare problemi di integrità dei dati.

Se stai definendo uno stato in cui la scuola studentesca è sconosciuta o vuota, il valore NULL non differenzierà queste condizioni. (di nuovo “torniamo allintegrità dei dati.) Il suggerimento della tabella dei ruoli di Tulains è elegante e consente valori nulli in modo pulito.