Nachteile der Verwendung eines nullbaren Fremdschlüssels anstelle der Erstellung einer Schnittpunkttabelle

Die beiden Modelle repräsentieren unterschiedliche Beziehungen.

Mithilfe einer Verknüpfungstabelle Sie modellieren eine Viele-zu-Viele-Beziehung.

Mit einem einfachen Fremdschlüssel modellieren Sie eine Eins-zu-Viele-Beziehung.

Der Nachteil eines nullbaren Fremdkörpers Der Schlüssel ist, dass die Beziehung nicht als viele-zu-viele-Beziehungen modelliert werden kann, wenn Sie dies erreichen möchten.

Basierend auf Ihrer Bearbeitung der Frage teilen Sie die Schülertabelle effektiv auf in zwei Tabellen mit dem gleichen Schlüssel. Ich sehe dies im Allgemeinen bei Tischen mit viel zu vielen Feldern, sodass jemand sie in zwei Teile aufteilt, um sie besser handhaben zu können (ich nenne es Lippenstift auf ein Schwein).

Indem Sie den Schülertisch aufteilen, machen Sie Die zweite Tabelle ist optional, da in der zweiten Tabelle kein Datensatz vorhanden sein muss. Dies ist einem Feld sehr ähnlich, das nicht festgelegt werden muss, da es null sein kann.

Wenn Sie eine Eins-zu-Viele-Beziehung wünschen, ist es weitaus besser, eine einzelne Tabelle zu verwenden und die Schul-ID zuzulassen in der Schülertabelle null sein. Es gibt keinen Grund, Nullen in Feldern zu vermeiden, selbst für einen Fremdschlüssel. Dies bedeutet, dass die Fremdbeziehung optional ist: Entwickler und Datenbankadministratoren verstehen dies eindeutig, und das zugrunde liegende Datenbankmodul sollte auf jeden Fall einwandfrei funktionieren.

Wenn Sie sich über Joins Sorgen machen, machen Sie sich keine Sorgen. Es gibt genau definierte Semantiken für die Funktionsweise von Joins mit Nullfeldern. Wenn Sie eine einzelne Tabelle verwenden, können Sie zwei statt drei Tabellen verknüpfen.

Kommentare

• Wenn ich also eine Eins-zu-Viele-Beziehung modelliere (mit optionaler Teilnahme auf der: 1-Seite), sollte ich einen Fremdschlüssel verwenden, obwohl er NULL -Werte haben kann?
• @Tom yes, that ist genau, wie man es modelliert. Obwohl es technisch möglich ist, eine Join-Tabelle zu verwenden, erlaubt das Datenmodell viele bis viele, sodass Sie Trigger und Datenbanklogik benötigen, um dies zu verhindern. Sie sind besser dran, wenn Sie die Beziehung so einschränken, dass keine falschen Daten hinzugefügt werden können.
• Ich habe meine Frage bearbeitet.Ich habe student_id nur zu einem Primärschlüssel in der Tabelle School_has_Student gemacht, wodurch die Beziehung als Eins-zu-Viele beibehalten wurde. Welche Nachteile hat diese Methode gegenüber der Verwendung eines Fremdschlüssels?
• @Tom Ich habe meine Antwort bearbeitet.

Sie haben oben in einem Kommentar geschrieben:

Das Buch „Fundamentals of Database Systems“ […] sagt [.. .] Es wird empfohlen, eine Schnittpunkttabelle zu verwenden, wenn die Fremdschlüsselspalte viele NULL-Werte enthält (z. B. wenn 98% der Mitarbeiter keine Abteilung verwalten).

Wenn die Fremdschlüsselspalte viele NULL-Werte enthält, müssen Ihre Programme diese meist leere Spalte für jeden von ihnen verarbeiteten Datensatz verarbeiten. Die Spalte belegt wahrscheinlich etwas Speicherplatz Obwohl in 98% aller Fälle die Beziehung leer ist, bedeutet das Abfragen der Beziehung das Abfragen der Spalte, die Ihnen mehr Netzwerkverkehr bietet. Wenn Sie ein ORM verwenden, das Ihre Klassen aus Ihren Tabellen generiert, benötigen Ihre Programme auch mehr Speicherplatz auf dem Client Seite als nötig. Mit einem inters Die Funktionstabelle vermeidet dies. Es sind nur Verknüpfungsdatensätze erforderlich, bei denen der entsprechende Fremdschlüssel sonst nicht NULL wäre.

Wenn Sie dagegen nicht nur einige NULL-Werte haben, sagen wir 50% oder mehr Relationen sind nicht NULL. Die Verwendung einer Kreuzungstabelle führt zu dem gegenteiligen Effekt: Mehr Speicherplatz, höhere Komplexität, was zu mehr Netzwerkverkehr usw. führt.

Die Verwendung einer Kreuzungstabelle ist also nur eine Form der Optimierung, die nur sinnvoll ist Ein spezieller Fall, und gerade heutzutage, wo Speicherplatz und Speicher billiger wurden, wird viel seltener benötigt. Beachten Sie, dass „Fundamentals of Database Systems“ ursprünglich vor mehr als 20 Jahren geschrieben wurde (ich fand einen Verweis auf die zweite Ausgabe von 1994), und ich denke, dass die Empfehlung zu diesem Zeitpunkt bereits vorhanden war. Vor 1994 war die Speicherplatzoptimierung wahrscheinlich viel wichtiger als heute, da Massenspeicher immer noch teurer waren und Computer und Netzwerke viel langsamer als heute.

Als Randnotiz zu einem wählerischen Kommentar: the Die obige Aussage versucht nur zu antizipieren, was der Autor von „Fundamentals of Database Systems“ mit seiner Empfehlung im Sinn hatte. Ich denke, er hat eine grobe, allgemeine Aussage gemacht, die für die meisten Systeme gültig ist. In einigen Datenbanken gibt es andere mögliche Optimierungen wie „spärliche Spalten“, die die Verwendung einer Schnittpunkttabelle noch veralteter machen.

Verstehen Sie diese Empfehlung also nicht falsch. Das Buch sagt nichts Sie bevorzugen Schnittpunkttabellen für {0,1}:n -Beziehungen im Allgemeinen oder – wie Sie geschrieben haben -, dass dies der „richtige Weg“ ist. Verwenden Sie solche Optimierungen, die Ihre Programme nur dann komplizierter machen, wenn Sie brauchen sie wirklich.

Kommentare

• Sie ‚ gehen viel von der Implementierung des aus Datenbank, insbesondere wenn man bedenkt, dass das OP ‚ keine bestimmte erwähnt. ‚ ist mehr als wahrscheinlich, dass die Datenbank intelligent genug ist, um verwendet zu werden nur wenig Platz für spärliche Spalten.
• @gardenhead: Was lässt Sie glauben, dass dies “ mehr als wahrscheinlich ist „?
• Die Tatsache, dass Datenbanken haben gibt es schon seit Jahrzehnten und sie sind hochoptimiert, da sie eine wichtige Komponente der meisten Infrastrukturen darstellen.
• @gardenhead: Klingt für mich so, als würden Sie viel ungerechtfertigtere Annahmen treffen als ich. Siehe jedoch meine Bearbeitung.

Das konzeptionelle Modell sieht folgendermaßen aus: sehr unorthodox um es weniger auszudrücken:

Das physikalische Modell sieht folgendermaßen aus. Dies ist verwirrend um es weniger zu sagen (die Leute werden denken, es ist M: M, wenn sie nicht genau hinsehen):

Mein Vorschlag:

Wenn Sie möchten, trennen viele Spalten (FK oder andere), die für die meisten Schüler nicht gelten, die Tabellen in Rollentabellen mit 1: 1-Rel. Aber das liegt nicht daran, dass sie FK sind. Dies liegt daran, dass die Spalten nicht für die meisten Zeilen gelten.

Andernfalls , nullfähige FK sind ein normaler Teil einer Datenbank und Verknüpfungstabellen sind normalerweise für M: M rels.

Häufige Verwendungen von 1: 1-Rels sind für Rollentabellen mit Spalten, die nur gelten, wenn die Entität von einem bestimmten Typ ist, und das Extrahieren von BLOB-Spalten aus Leistungs- oder Speichergründen. Das Avodieren von Nullwerten in FKs ist dafür keine übliche Verwendung.

Zusätzlich zu anderen Antworten möchte ich darauf hinweisen, dass ein Nullwert für den Fremdschlüssel nicht eindeutig ist. Bedeutet dies:

1) Die Schule des Schülers (falls vorhanden) ist unbekannt (dies ist die Standardbedeutung von „null“ – Wert ist unbekannt)

2) Es ist bekannt, ob der Schüler eine Schule hat oder nicht, und sie haben keine

Wenn Sie die Standardbedeutung von null verwenden, wie würden Sie „Schüler hat keine Schule“ in Ihrem Fremdschlüsselmodell darstellen. In diesem Fall Sie müssten wahrscheinlich einen Eintrag „keine Schule“ mit seiner eigenen ID in der Schultabelle erstellen. (Nicht ideal)

Kommentare

• Das Buch “ Grundlagen von Datenbanksystemen “ erwähnt, dass es 3 Interpretationen für NULL kann bedeuten: 1) Unbekannter Wert. 2) Nicht verfügbarer oder zurückgehaltener Wert. 3) Nicht zutreffendes Attribut (Ich denke, diese Interpretation bedeutet, dass Sie ein NULL für einen Fremdschlüssel).
• Diese ‚ ist eine nützliche Liste, aber die Semantik von null (oder einem beliebigen Wert wirklich) ist benutzerdefinierbarkann bedeuten, was auch immer der Designer sagt, es ist nicht auf diese Liste beschränkt. Die Frage ist, wie verschiedene Bedeutungen unterschieden werden können, wenn möglicherweise mehr als eine erforderlich ist (oder sogar unbeabsichtigt gespeichert wird).
• Schlagen Sie also vor, dass ich eine Schnittpunkttabelle erstellen sollte, anstatt einen nullbaren Fremdschlüssel zu verwenden?
• @Tom Ja, ich glaube, das ist in diesem Fall besser.
• @BradThomas – um die gleiche Mehrdeutigkeit bei der Verwendung einer Schnittpunkttabelle zu vermeiden, würden Sie Fall 2 darstellen (es ist bekannt, dass der Schüler dies hat keine Schule) durch einen Datensatz in der Schnittpunkttabelle mit einer NULL School_ID?

Datenbanktabellen haben dies schöne Sache namens Einschränkungen. Es ist also sehr einfach, eine Kreuzungstabelle zu erstellen, in der nur einer von jedem Schüler in der Tabelle angezeigt wird, aber viele Schulen in dieser Tabelle. Es ist schön, Ihnen eine

Theorie zu geben, aber am Ende sind Sie es Modellieren Sie Ihre Datenbank nach den Fragen, die Sie stellen.

Wenn Sie häufig Fragen mit der Frage stellen möchten: „Welche Schüler sind in meiner Schule?“, möchten Sie wirklich die gesamte Schülertabelle abfragen oder haben Eine einfache Schnittstellentabelle.

In Datenbanken: Optimieren Sie für die Fragen, die Sie stellen.

Dort ist ein Anwendungsfall, bei dem die Verwendung einer dritten Tabelle tatsächlich sinnvoll sein kann. Das Beispiel mag rein hypothetisch erscheinen, aber ich hoffe, es veranschaulicht meinen Standpunkt gut. Nehmen wir an, Sie fügen der Tabelle students weitere Spalten hinzu, und irgendwann entscheiden Sie sich, die Eindeutigkeit der Datensätze über einen zusammengesetzten Index für mehrere Spalten zu erzwingen. Es ist sehr wahrscheinlich, dass Sie dies tun „Ich muss auch die Spalte school_id einfügen, und hier wird es langsam chaotisch. Aufgrund der Art und Weise, wie SQL entworfen wurde, werden mehrere identische Datensätze eingefügt, in denen school_id ist NULL ist möglich. Aus technischer Sicht ist dies durchaus sinnvoll, aber nicht intuitiv und kann zu unerwarteten Ergebnissen führen Schnittpunkttabelle ist einfach.

Ich musste kürzlich eine solche „optionale“ Beziehung modellieren, bei der die Anforderung für eine Eindeutigkeitsbeschränkung auf eine Zeitstempelspalte zurückzuführen war. Das Verlassen des nullbaren Fremdschlüssels in der Tabelle führte plötzlich zu Möglichkeit des Einfügens von Datensätzen mit demselben Zeitstempel (nehmen wir an, dass es sich um einen Standardstempel handelt, der für Datensätze festgelegt wurde, die nicht geprüft wurden / genehmigt wurden) noch nicht erledigt) – und der einzige Ausweg bestand darin, eine nullfähige Spalte zu entfernen.

Wie Sie sehen können, ist dies ein ziemlich spezifischer Fall, und wie andere angemerkt haben, wären Sie meistens mit allen vollkommen in Ordnung die NULL -Werte. Es hängt wirklich von den spezifischen Anforderungen Ihres Modells ab.

Zusätzlich zu den vielen guten Vorschlägen, die ich persönlich eingereicht habe „Ich bin kein Fan von Fremdschlüsseln, es sei denn, sie sind wirklich notwendig. Erstens gibt es die M: M-Beziehung, auf die Sie verweisen. Außerdem führt das Aufrufen eines Fremdschlüssels und das Abrufen dieser Tabellendaten in Ihre Abfragen zu einer höheren Komplexität und Abhängigkeit Tabellengröße, langsamere Leistung. Wie bereits erwähnt, können nullfähige FK-Felder nicht unterstützt werden und Datenintegritätsprobleme verursachen.

Wenn Sie einen Status definieren, in dem die Schülerschule unbekannt oder leer ist, wird NULL verwendet wird diese Bedingungen nicht unterscheiden. (Wir kehren wieder zur Datenintegrität zurück.) Der Rollentabellenvorschlag von Tulains ist elegant und lässt Nullwerte sauber zu.