Wie werden Super- und Subtyp-Beziehungen in ER-Diagrammen als Tabellen dargestellt?

ER Notation

Es gibt mehrere ER-Notationen. Ich bin nicht vertraut mit dem, den Sie verwenden, aber es ist klar genug, dass Sie versuchen, einen Subtyp zu repräsentieren (aka. Vererbung, Kategorie, Unterklasse, Generalisierungshierarchie ...). Dies ist der relationale Cousin der OOP-Vererbung.

Wenn Sie Subtyping durchführen, sind Sie im Allgemeinen mit folgenden Design-Entscheidungen konfrontiert:

• Abstrakt vs. konkret: Kann der Elternteil instanziiert werden? In Ihrem Beispiel: kann ein Vehicle existieren ohne auch 2WD oder 4WD ? ¹
• Inklusive oder exklusiv: Kann mehr als ein Kind für denselben Elternteil instanziiert werden? In Ihrem Beispiel kann Vehicle sowohl 2WD als auch 4WD ? ² sein
• Abgeschlossen oder unvollständig: Erwarten Sie, dass in Zukunft mehr Kinder hinzugefügt werden? Erwarten Sie in Ihrem Beispiel, dass ein Bike oder ein Plane (etc ...) später zum Datenbankmodell hinzugefügt werden kann?

Die Information Engineering-Notation unterscheidet zwischen inklusiver und exklusiver Subtyp-Beziehung. Die IDEF1X-Notation hingegen erkennt diesen Unterschied nicht (direkt), unterscheidet aber zwischen einem vollständigen und einem unvollständigen Subtyp (was IE nicht tut).

Das folgende Diagramm aus dem ERwin-Methodenhandbuch (Kapitel 5, Untertypbeziehungen) ) illustriert den Unterschied:

Weder IE noch IDEF1X erlauben direkt die Angabe von abstrakt und konkret übergeordnet.

Physische Darstellung

Leider unterstützen praktische Datenbanken die Vererbung nicht direkt, daher müssen Sie dieses Diagramm in echte Tabellen umwandeln . Hierfür gibt es grundsätzlich drei Ansätze:

1. Platzieren Sie alle Klassen in derselben Tabelle und lassen Sie untergeordnete Felder NULL-fähig. Sie können dann eine Überprüfung durchführen, um sicherzustellen, dass die richtige Teilmenge der Felder nicht NULL ist.
   • Pros: Kein JOINing, also können einige Abfragen davon profitieren. Kann Schlüssel auf Parent-Ebene erzwingen (z. B. wenn Sie verschiedene 2WD und 4WD Fahrzeuge mit derselben ID vermeiden möchten). Kann problemlos einschließende vs. ausschließliche Kinder und abstrakte vs. konkrete Eltern erzwingen (indem man einfach die CHECK variiert).
   • Nachteile: Einige Abfragen können langsamer sein, da sie "uninteressante" Kinder herausfiltern müssen. Abhängig von Ihrem DBMS können untergeordnete Einschränkungen problematisch sein. Viele NULL können Speicher verschwenden. Weniger geeignet für unvollständige Subtypisierung - das Hinzufügen eines neuen Kinds erfordert die Änderung der vorhandenen Tabelle, was in einer Produktionsumgebung problematisch sein kann.
2. Alle untergeordneten Elemente in separate Tabellen einfügen, aber keine Tabelle für das übergeordnete Element (stattdessen die Felder und Einschränkungen des übergeordneten Elements in allen untergeordneten Elementen wiederholen). Hat die meisten der Eigenschaften von (3) unter Vermeidung von JOINs, zum Preis geringerer Wartbarkeit (aufgrund all dieser Feld- und Constraint-Wiederholungen) und Unfähigkeit, Schlüssel auf Elternebene zu erzwingen oder ein konkretes Elternteil darzustellen.
3. Platzieren Sie Eltern und Kinder in separaten Tabellen.
   • Vorteile: Sauber. Keine Felder / Beschränkungen müssen künstlich wiederholt werden. Erzwingt Schlüssel auf übergeordneter Ebene und fügt kinderspezifische Einschränkungen hinzu. Geeignet für unvollständiges Subtyping (relativ einfach weitere untergeordnete Tabellen hinzufügen). Bestimmte Abfragen können nur von "interessanten" untergeordneten Tabellen profitieren.
   • Nachteile: Einige Abfragen können JOIN-schwer sein. Es kann schwierig sein, Inclusive vs. Exclusive Children und Abstract vs Concrete Parent zu erzwingen (diese können deklarativ erzwungen werden, wenn das DBMS zirkuläre und verzögerte Fremdschlüssel unterstützt, aber die Durchsetzung auf Anwendungsebene wird normalerweise als a betrachtet kleineres Übel).

Wie Sie sehen, ist die Situation nicht ideal - Sie müssen Kompromisse eingehen, unabhängig davon, welchen Ansatz Sie wählen. Der Ansatz (3) sollte wahrscheinlich Ihr Ausgangspunkt sein, und wählen Sie nur eine der Alternativen, wenn es einen zwingenden Grund dafür gibt.

¹ Ich nehme an, das ist die Stärke der Linie in Ihren Diagrammen.

² Ich vermute, das ist es, was für das Vorhandensein oder Fehlen von "disjoint" in Ihren Diagrammen steht.

Was andere Antwortende gesagt haben, plus das Folgende, das in Primärschlüssel für Unterklassen-Tabellen einfließt.

Ihr Fall sieht wie eine Instanz des Entwurfsmusters aus, das als "Generalisierungsspezialisierung" oder kurz Gen-Spec bezeichnet wird. Die Frage, wie gen-spec mit Hilfe von Datenbanktabellen modelliert wird, kommt in SO immer wieder auf.

Wenn Sie gen-spec in einer OOPL wie Java modellieren würden, würden Sie die Unterklassen-Vererbungsfunktion verwenden, um sich um die Details für Sie zu kümmern. Sie würden einfach eine Klasse definieren, die sich um die verallgemeinerten Objekte kümmert, und dann eine Sammlung von Unterklassen definieren, eine für jeden Typ eines spezialisierten Objekts. Jede Unterklasse würde die verallgemeinerte Klasse erweitern. Es ist einfach und unkompliziert.

Leider enthält das relationale Datenmodell keine Unterklassenvererbung, und die SQL-Datenbanksysteme bieten meines Wissens keine solche Möglichkeit. Aber du bist kein Pech. Sie können Ihre Tabellen so entwerfen, dass sie gen-spec so modellieren, dass sie der Klassenstruktur von OOP entsprechen. Sie müssen dann Ihren eigenen Vererbungsmechanismus implementieren, wenn neue Elemente zur verallgemeinerten Klasse hinzugefügt werden. Details folgen.

Die Klassenstruktur ist ziemlich einfach, mit einer Tabelle für die Genklasse und einer Tabelle für jede Spezifikationsunterklasse. Hier ist eine schöne Illustration von Martin Fowlers Website. Klassentabellenvererbung Beachten Sie, dass in diesem Diagramm Cricketer sowohl eine Unterklasse als auch eine Oberklasse ist. Sie müssen auswählen, welche Attribute in welche Tabellen gehören. Das Diagramm zeigt ein Beispielattribut in jeder Tabelle.

Das heikle Detail ist, wie Sie Primärschlüssel für diese Tabellen definieren. Die Gen-Klasse-Tabelle erhält einen Primärschlüssel in der üblichen Weise (es sei denn, diese Tabelle ist eine Spezialisierung einer weiteren Verallgemeinerung, wie Cricketers). Die meisten Designer geben dem Primärschlüssel einen Standardnamen wie "Id". Sie verwenden die automatische Nummerierung, um das Id-Feld zu füllen. Die Spec-Class-Tabellen erhalten einen Primärschlüssel, der "Id" genannt werden kann, aber die Auto-Nummer-Funktion wird nicht verwendet. Stattdessen wird der Primärschlüssel jeder Unterklassentabelle eingeschränkt, um auf den Primärschlüssel der verallgemeinerten Tabelle zu verweisen. Dies macht jeden der spezialisierten Primärschlüssel zu einem Fremdschlüssel sowie einem Primärschlüssel. Beachten Sie, dass im Falle von Cricketers das Id-Feld auf das Id-Feld in Playern verweist, das Id-Feld in Bowlern jedoch auf das Id-Feld in Cricketers.

Wenn Sie jetzt neue Elemente hinzufügen, müssen Sie die referenzielle Integrität beibehalten. So geht's.
Sie fügen zuerst eine neue Zeile in die Gen-Tabelle ein und stellen Daten für alle Attribute außer dem Primärschlüssel bereit. Der automatische Nummerierungsmechanismus generiert einen eindeutigen Primärschlüssel. Als Nächstes fügen Sie eine neue Zeile in die entsprechende Spezifikationstabelle ein, einschließlich Daten für alle Attribute einschließlich des Primärschlüssels. Der Primärschlüssel, den Sie verwenden, ist eine Kopie des gerade generierten Primärschlüssels. Diese Propagierung des Primärschlüssels kann als Vererbung des armen Mannes bezeichnet werden.

Wenn Sie jetzt alle verallgemeinerten Daten zusammen mit allen spezialisierten Daten aus nur einer Unterklasse benötigen, müssen Sie nur die beiden Tabellen über die gemeinsamen Schlüssel verbinden. Alle Daten, die nicht zu der betreffenden Unterklasse gehören, werden aus dem Join gelöscht. Es ist glatt, leicht und schnell.

Wenn Sie in Ihrem Datenbankentwurf eine Beziehung vom Typ Super-Typ / Untertyp verwenden, müssen Sie normalerweise eine separate Tabelle für Ihren Typ der allgemeinen Entität (Super-Typ) und separate Tabellen für Ihre Version (en) der spezialisierten Entität erstellen -Typ) unzusammenhängend oder nicht. In Ihrem Fall müssen Sie eine Tabelle für VEHICLE und einen Primärschlüssel sowie einige Attribute erstellen, die allen Untertypen gemeinsam sind oder von ihnen geteilt werden. Dann müssen Sie separate Tabellen für den 2WD und 4WD zusammen mit Attributen erstellen, die nur für diese Tabellen spezifisch sind. Zum Beispiel

Dann könnten Sie diese Tabellen mit SQL-Joins

Es gibt nicht immer nur eine Möglichkeit, ein bestimmtes Datenmodell zu implementieren. Häufig tritt eine Transformation auf, wenn Sie von einem logischen Modell zu einem physischen Modell wechseln.

Standard-SQL bietet keine saubere Möglichkeit, disjunkte Subtype-Einschränkungen zu erzwingen.

Wenn Sie so viele Regeln Ihres Modells wie möglich mithilfe des Schemas erzwingen möchten, verwenden Sie standardmäßig eine Tabelle für den Supertyp und eine für jeden Subtyp. Dies stellt sicher, dass nur anwendbare Attribute für jede Entität verwendet werden.

Es gibt einen mehr oder weniger standardmäßigen SQL-Trick zum Erzwingen der disjunkten Einschränkung. Es bringt einige Leute davon ab, weil es die Normalisierungsregeln auf unwichtige Weise verletzt. Dennoch finden manche Leute die Technik ästhetisch beleidigend, da es eine technische Verletzung von 2NF gibt.

Diese Technik beinhaltet das Hinzufügen eines Partitionierungsattributs zum Supertyp und das Einbeziehen dieses Partitionierungsattributs in jeden Subtyp, indem es zum Primärschlüssel des Subtyps hinzugefügt wird. Zusammen mit Prüfbedingungen, die bestimmte Werte für die Partitionierungsattribute festlegen, wird sichergestellt, dass jede Entität höchstens einen Subtyp haben kann. Die Technik ist an vielen Stellen detailliert dokumentiert, zum Beispiel dieser Blog .