Svantaggi dell'uso di una chiave esterna nullable invece di creare una tabella di intersezione

I due modelli rappresentano relazioni diverse.

Usando 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 una chiave straniera nullable non è in grado di modellare la relazione come molti a molti, se questo è ciò che stai cercando di ottenere.

In base alla tua modifica alla domanda, stai dividendo in modo efficace il tavolo dello studente 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 (io lo chiamo mettendo il rossetto su un maiale).

Suddividendo la tabella degli studenti, si rende facoltativa la seconda tabella poiché non è necessario che un record sia presente nella seconda tabella. Che è molto simile a un campo che non ha bisogno di essere impostato perché può essere nullo.

Se si desidera una relazione uno-a-molti, è molto meglio utilizzare una singola tabella e consentire all'ID della scuola di essere nullo nella tabella degli studenti. Non vi è alcun motivo per evitare valori nulli nei campi, anche per una chiave esterna. Ciò significa che la relazione straniera è facoltativa: gli sviluppatori e gli amministratori di database capiscono chiaramente che il motore di database sottostante dovrebbe funzionare correttamente.

Se sei preoccupato per i join, non preoccuparti. Esistono semantiche ben definite per il modo in cui i join funzionano con campi null. Usando una singola tabella, puoi unire due tabelle anziché tre.

Hai scritto in un commento sopra:

the book "Fundamentals of Database Systems" [...] says [...] that it is recommended to use an intersection table if there are a lot of NULL values in the foreign key column (for example: if 98% of employees don't manage a department)

Quando ci sono molti valori NULL nella colonna chiave esterna, i tuoi programmi dovranno fare i conti con questa colonna prevalentemente vuota per ogni record che elaborano. La colonna occuperà probabilmente dello spazio su disco anche se nel 98% dei casi è vuota, interrogando la relazione significa interrogando quella colonna che ti dà più traffico di rete, e se stai usando un ORM che genera classi dalle tue tabelle, i tuoi programmi avrà anche bisogno di più spazio sul lato client del necessario. L'utilizzo di una tabella di intersezioni evita questo, saranno necessari solo i record di collegamento in cui la chiave esterna equivalente non sarebbe altrimenti NULL.

Contro questo, se non hai solo pochi valori NULL, diciamo che il 50% o più relazioni non sono NULL, utilizzando una tabella di intersezione si ottiene l'effetto opposto: più spazio su disco, maggiore complessità con conseguente più traffico di rete ecc. .

Quindi usare una tabella di intersezione è solo una forma di ottimizzazione, solo sensibile per un caso specifico, e soprattutto oggigiorno, dove lo spazio su disco e la memoria sono diventati meno costosi, molto meno frequentemente necessari. Si noti che "Fundamentals of Database Systems" è stato originariamente scritto più di 20 anni fa (ho trovato un riferimento alla seconda edizione del 1994), e suppongo che la raccomandazione fosse già lì in quel momento. Prima del 1994, l'ottimizzazione dello spazio era probabilmente molto più importante di oggi, dato che lo storage di massa era ancora più costoso e computer e reti erano molto più lenti di oggi.

Come nota a margine di un commento schizzinoso: la dichiarazione di cui sopra sta solo cercando di anticipare ciò che l'autore di "Fundamentals of Database Systems" aveva in mente con la sua raccomandazione, immagino stia facendo una dichiarazione generale approssimativa , valido per la maggior parte dei sistemi. In alcuni database ci sono altre possibili ottimizzazioni come "colonne sparse" che rendono l'uso di una tabella di intersezione ancora più obsoleto.

Quindi non ottenere questa raccomandazione sbagliata. Il libro non ti dice di preferire le tabelle di intersezione per le relazioni {0,1}:n in generale, o - come hai scritto tu - che questo è il "modo corretto". Usa ottimizzazioni come questa che renderà i tuoi programmi più complicati solo quando ne avrai davvero bisogno.

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

Ilmodellofisicosaràsimileaquesto,cheèconfusoperdiredimeno(lagentepenseràchesiaM:Mamenochenonlovedanodavicino):

Ilmiosuggerimento:

Setipiace,moltecolonne(FKoaltro),chenonsiapplicanoallamaggiorpartedeglistudenti,separanoletabelleintabellediruolocon1:1rel.ManonèperchésonoFK,èperchélecolonnenonsiapplicanoallamaggiorpartedellerighe.

Altrimenti,FKNullsonounapartenormalediundatabaseeletabelledijoinsonoingenereperM:Mrels.

Gliusicomunidi1:1relsonoperletabellediruoloconcolonnechesiapplicanosolosel'entitàèdiuncertotipoedestraecolonneBLOBperconsiderazionisulleprestazioniosull'archiviazione.AvvalersidivalorinullinFKnonèunusocomuneperquesto.

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

1) La scuola dello studente (se presente) è sconosciuta (questo è il significato standard di 'null' - il valore è sconosciuto)

2) È noto se lo studente ha o meno una scuola, e non ne hanno nessuno

Se si utilizza il significato standard di null, come rappresenterebbe "lo studente non ha scuola" nel modello di chiave esterna. In tal caso, probabilmente dovresti creare una voce "nessuna scuola", con il proprio ID nel tavolo della scuola. (Non ideale)

Le tabelle di database hanno questa bella cosa chiamata vincoli. Quindi è molto facile da fare nella tabella delle intersezioni che consente di visualizzare solo 1 di ogni studente nella tabella ma molte scuole in quella tabella. Effettivamente dandoti un

La teoria è bella, ma alla fine modellerai il tuo database dopo le domande che stai chiedendo.

Se vuoi fare domande spesso con la domanda: "quali studenti sono nella mia scuola" vuoi veramente interrogare l'intera tabella degli studenti o avere una tabella di intersezione facile.

Nei database: ottimizza le domande che chiedi.

C'è un caso d'uso in cui l'uso di un terzo tavolo può avere un senso. L'esempio 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 applicare l'unicità ai record tramite l'indice composito su più colonne. È molto probabile che dovrai includere anche la colonna school_id , e qui le cose cominciano a diventare disordinate. A causa del modo in cui è stato progettato SQL, sarà possibile inserire diversi record identici in cui school_id è NULL . Ha perfettamente senso dal punto di vista tecnico, ma è controintuitivo e può portare a risultati inaspettati. D'altra parte, imporre l'unicità sulla tabella di intersezione è facile.

Ho dovuto modellare recentemente una relazione "facoltativa", in cui il requisito per un vincolo di unicità era dovuto a una colonna timestamp. Lasciare la chiave straniera nullable nella tabella porta improvvisamente alla possibilità di inserire record con lo stesso timestamp (supponiamo che sia uno predefinito, impostato su record che non sono stati ancora verificati / approvati) - e l'unica via d'uscita è stata quella di rimuovere colonna nullable.

Quindi, come puoi vedere, è un caso abbastanza specifico, e come altri hanno notato, la maggior parte delle volte starai perfettamente d'accordo con tutti i valori di NULL . Dipende davvero dai requisiti specifici del tuo modello.

Oltre ai molti buoni suggerimenti già presentati, personalmente non sono un fan delle chiavi straniere a meno che non siano veramente necessarie. Innanzitutto c'è la relazione M: M a cui stai facendo riferimento. Inoltre, la chiamata a una chiave esterna e quindi il recupero dei dati della tabella nelle query, introduce una maggiore complessità e, a seconda delle dimensioni della tabella, prestazioni più lente. Come altri hanno già detto, i campi FK Null possono essere non supportati e possono creare problemi di integrità dei dati.

Se stai definendo uno stato in cui la scuola dello studente è sconosciuta o vuota, il NULL non differenzia queste condizioni. (ancora una volta torniamo all'integrità dei dati.) Il suggerimento della tabella dei ruoli di Tulains è elegante e consente di ottenere valori nulli in modo pulito.