Manchmal stoße ich bei der Optimierung einer Kundenabfrage an eine Grenze. Über die Jahre habe ich bemerkt, dass sich die meisten dieser Fälle in zwei Kategorien einordnen lassen: (1) unrealistische Erwartungen auf Basis der Cloud-Rechnung, anstatt auf Basis der Leistungsfähigkeit der Infrastruktur; (2) die Primärschlüssel. Dieser Artikel zeigt, wie Primärschlüssel Tabellen voneinander abgrenzen können und damit ein normalisiertes Datenbank-Schema derart in Teile zerschmettern, dass SQL’s mengenbasierte Fähigkeiten effektiv unterbunden werden. Ich stelle auch eine alternative Methode zur Gestaltung von Primärschlüssel vor und zeige die Vor- und Nachteile. Ich verwende den Begriff Strukturierte Primärschlüssel, um die Unterschiede gegenüber natürlichen und künstlichen Schlüsseln hervorzuheben.
Kunden
Das kleine Datenbankschema, dass ich in diesem Artikel verwende, ist für einen einfachen Web-Shop. Beginnen wir mit dem Kern der Kunden-Tabelle customers:
CREATE TABLE customers (
name VARCHAR NOT NULL,
email VARCHAR NOT NULL,
/* weitere Attribute */
/* TODO: PRIMARY KEY */
)Ich habe die Tabelle auf das Wesentliche reduziert, da es in diesem Artikel nicht um die geschäftlichen Spalten geht.0 Die zwei gezeigten Spalten dienen nur dem Zweck die wesentliche Frage stellen zu können: Was ist ein guter Primärschlüssel für diese Tabelle?
Da es in diesem Artikel nur nebensächlich um natürliche und künstliche Schlüssel geht, möchte ich es an dieser Stelle kurz machen: Keine der gezeigten Spalten kommt für die Aufnahme in einen Primärschlüssel infrage.1 Der Grund ist, dass beide Spalten einer extern definierte Semantik unterliegen. Das heißt, wir kennen die Regeln, denen sie folgen, nicht. Insbesondere wissen wir nicht ob und wann sie eindeutig sind. klar, bei Namen wissen wir, dass sie nicht eindeutig sind. Es ist aber auch bei E-Mail-Adressen so und generell immer dann, wenn die Regeln der Eindeutigkeit von jemand anderem definiert werden.
Beachte, dass ich das Argument, das Primärschlüsselwerte unveränderlich sein sollten, nicht verwende. Aber auch nicht widerspreche. Da sich Primärschlüsselwerte oft in viele Tabellen und Zeilen übertragen werden, kann das Ändern dieser Werte schwierig werden.2 Primärschlüssel hinterlassen oft auch Spuren außerhalb der Datenbank (Interfaces, Logfiles, Ausdrucke, …), die dann durch Änderungen bedeutungslos oder gar gefährlich werden. Obwohl beide Argument aus praktischer Sicht korrekt sind, bevorzuge ich es ein noch stärkeres Argument zu verwenden, dass letztendlich eine Voraussetzung zur Funktion eines relationalen Modells ist.
Dieses Argument ist die Unveränderlichkeit der Eindeutigkeitsregeln. Beachte die Hervorhebung: Unveränderliche Eindeutigkeitsregeln sind unbedingt erforderlich, unveränderliche Werte nicht unbedingt! Nehmen wir E-Mail-Adressen als Beispiel. Dabei ist es ein Problem, dass die Eindeutigkeitsregeln von E-Mail-Adressen von Mailserver des Empfängers abhängig sind. Konkret sollte der Teil vor dem „@“-Zeichen zwischen Groß- und Kleinschreibung unterscheiden, was meist nicht getan wird, Unteraddressen („+“-Addressen) könnten, müssen aber nicht unterstützt werden, Punkte könnten ignoriert werden. Stell dir das vor: Während a.b@gmail.com und ab@gmail.com dieselbe Mailbox meinen, ist es bei anderen Domains nicht so.
Das Problem ist, dass die Eindeutigkeitsregeln extern definiert sind. Das bedeutet, dass wir sie unter Umständen nicht vollständig verstehen, vor allem aber auch, dass sie sich künftig ändern könnten. Damit solche Missverständnisse oder Änderungen die Eindeutigkeit der Primärschlüssel nicht torpedieren, dürfen wir solche Werte nicht in Primärschlüssel aufnehmen. Im Ergebnis stimmt die Geschichte bisher also mit dem „Verwende immer einen künstlichen Schlüssel“-Mantra überein. Das war aber erst der Anfang. Im weiteren verwende ich die folgende Definition der Tabelle customers.
CREATE TABLE customers (
name VARCHAR NOT NULL,
email VARCHAR NOT NULL,
/* weitere Attribute */
id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
PRIMARY KEY (id)
)Bestellungen
Die zweite Tabelle im Shop ist für Bestellungen.
CREATE TABLE orders (
customer_id BIGINT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (id),
placed TIMESTAMP(6) NOT NULL,
/* weitere Attribute */
/* TODO: PRIMARY KEY */
)Ich habe wieder auf die relevanten Teile gekürzt. Das Wichtigste ist der Bezug zum Kunden in Form eines Fremdschlüssels. Weiters habe ich eine Spalte für den Zeitpunkt der Platzierung der Bestellung definiert. Natürlich braucht so eine Tabelle noch weitere Spalten, sie sind für die zentrale Frage aber unwichtig: Was ist ein guter Primärschlüssel für diese Tabelle?
Die Situation ist diesmal etwas anders. Die Kombination aus (customer_id, placed) ist durchaus eine Überlegung wert. Obwohl die Eindeutigkeitsregeln der Zeit ebenfalls extern vorgegeben sind – von unserem Universum, auf verwirrende Weise –, könnte man argumentieren, dass sie für den Zweck eines Shops hinreichend bekannt und unveränderlich sind. Der Zeitpunkt eines vergangenen Ereignisses, wie das Aufgeben einer Bestellung, hat sogar die wünschenswerte Eigenschaft unveränderlich zu sein. Wenn der Zeitstempel eine ausreichende Auflösung hat, kann man ihn sogar als ausreichend eindeutig ansehen. Die Konsequenz eines Primärschlüssel aus den beiden Spalten customer_id und placed ist, dass ein einzelner Kunde keine zwei Bestellungen in derselben Mikrosekunde aufgeben kann. Oder anderes herum: Dieser Primärschlüssel setzt unserem Shop ein hartes Limit von einer Million Bestellungen pro Kunde und Sekunde. Ob das eine akzeptable Einschränkung ist, muss jeder selbst beurteilen. Im weiteren Verlauf des Artikels erübrigt sie sich ohnehin. Wenn man diese Einschränkungen akzeptiert, gibt es keinen zwingenden Grund gegen die Verwendung der Kombination von customer_id und placed als Primärschlüssel. Sogar die wünschenswerte, wenn auch nicht erforderliche Eigenschaft der Unveränderlichkeit der Werte ist gegeben. Gründe die dagegen sprechen sind, dass Zeitstempel relativ viel Speicher benötigen↓, und dass sie für Menschen sehr unpraktisch sind. Man stelle sich nur vor, man muss einen Zeitstempel am Telefon durchgeben.
Ich will dennoch Alternativen betrachten. Wenn man den Zeitstempel nicht als Teil eines Primärschlüssel akzeptiert, hat die Tabelle keinen Schlüsselkandidaten. Dann muss man wohl, wie zuvor, einen machen. Und schon wieder scheint das „verwende immer einen künstlichen Schlüssel“-Mantra recht zu behalten, nur dass es diesmal zu massiven Problemen führt.
CREATE TABLE orders (
customer_id BIGINT NOT NULL,
FOREIGN KEY (customer_id)
REFERENCES customers (id),
placed TIMESTAMP(6) NOT NULL,
/* weitere Attribute */
id BIGINT NOT NULL
GENERATED ALWAYS AS IDENTITY,
PRIMARY KEY (id)
)Kannst du das Problem sehen? Lass mich dir zeigen, wie dieser Primärschlüssel das Schema in Scherben zerschmettert…
Bestellte Produkte
Dafür benötigen wir noch eine dritte Tabelle, für die Produkte einer einzelnen Bestellung.
CREATE TABLE order_lines (
order_id BIGINT NOT NULL,
FOREIGN KEY (order_id)
REFERENCES orders (id),
product_id INTEGER NOT NULL,
qty INTEGER NOT NULL CHECK (qty > 0),
/* more attributes */
/* TODO: PRIMARY KEY */
)Diese Tabelle bildet letztendlich den bestellen Einkaufswagen ab. Sie enthält eine Zeile für jedes bestellte Produkt. Die Tabelle hat einen Fremdschlüssel zur Orders-Tabelle aber noch einen Primärschlüssel. Der Primärschlüssel dieser Tabelle ist für das Problem auch völlig unerheblich.
Um das Problem zu verstehen, muss man sich Abfragen wie die folgende ansehen: Wann hat ein Kunde ein bestimmtes Produkt das letzte Mal bestellt?
SELECT placed
FROM order_lines ol
JOIN orders o
ON o.id = ol.order_id
WHERE customer_id = ?
AND product_id = ?
ORDER BY placed DESC
FETCH FIRST 1 ROW ONLYDas ist eine Top-N-Abfrage in Standard-SQL-Syntax. Falls du mit der Fetch First-Klausel nicht vertraut bist: Sie macht in diesem Fall dasselbe wie limit 1 oder select top 1. Da die Abfrage Spalten aus zwei Tabellen benötigt, ist der Join keine Überraschung. Außer man denkt daran, dass der Primärschlüssel der Orders-Tabelle auch (customer_id, placed) hätte sein können.
In diesem Fall würde die Definition der Order_lines-Tabelle nämlich so aussehen:
CREATE TABLE order_lines (
customer_id BIGINT NOT NULL,
order_placed TIMESTAMP(6) NOT NULL,
FOREIGN KEY (customer_id, order_placed)
REFERENCES orders (customer_id, placed),
product_id INTEGER NOT NULL,
qty INTEGER NOT NULL CHECK (qty > 0),
/* weitere Attribute */
/* TODO: PRIMARY KEY */
)Da die Tabelle einen Fremdschlüssel zur Orders-Tabelle hat, müssen die Primärschlüssel-Spalten der Orders-Tabelle in die Order_lines-Tabelle übernommen werden. Das führt zu dem Effekt, dass die vorherige Abfrage keinen Join mehr benötigt.
SELECT order_placed
FROM order_lines ol
WHERE customer_id = ?
AND product_id = ?
ORDER BY order_placed DESC
FETCH FIRST 1 ROW ONLYDie Customer_id sowie der Zeitpunkt der Bestellung sind nun leicht erhältlich direkt neben der Produkt_id abgespeichert. Die folgende Balkengrafik zeigt zuerst die Antwortzeit der ersten Abfrage als Basis (100%). Die zwei unteren Balken zeigen die relative Antwortzeit der Abfrage ohne Join. Bei einem DBMS (S1) ist die relative Antwortzeit ein Zehntel, bei der anderen eher ein Viertel. Und das kommt nicht vom Entfernen des Joins!
Um das zu zeigen, verwendet die nächste Abfrage wieder einen Join, obwohl alle Daten in einer Tabelle verfügbar sind. Die Abfrage greift auf die Customer_id und Placed-Spalten aus der Orders-Tabelle zu, als ob sie in der Order_lines-Tabelle nicht verfügbar wären. Die einzigen Zugriffe auf die Order_lines-Tabelle sind in der Join-Bedingung und in der Where-Klausel für die Einschränkung auf ein bestimmtes Produkt. Insbesondere ist weder in der Select- noch in der Order By-Klausel ein Zugriff auf die Order_lines-Tabelle.
SELECT o.placed
FROM order_lines ol
JOIN orders o
ON o.customer_id = ol.customer_id
AND o.placed = ol.order_placed
WHERE o.customer_id = ?
AND ol.product_id = ?
ORDER BY o.placed DESC
FETCH FIRST 1 ROW ONLYDie Antwortzeit steigt durch diesen Join nur wenige Prozentpunkte. Selbst mit einem Join macht sie der strukturierten Primärschlüssel um Faktoren schneller.
Der Join selbst ist also nicht das Problem. Die Grenze, die der unstrukturierte Primärschlüssel orders.id aufbaut, ist das Problem. Die folgenden Diagramme verdeutlichen das. Das erste zeigt die Variante mit dem einspaltigen Primärschlüssel auf der Order-Tabelle. Die Pfeile stellen die Fremdschlüssel dar. Die Enden der Pfeile sind jeweils bei den Spalten des Fremdschlüssels.
Diese Orders-Tabelle trennt die Tabellen Customers und Order_lines voneinander, weil der „eingehende“ und der „ausgehende“ Fremdschlüssel verschiedene Spalten treffen. Jede Abfrage, die sowohl Kunden, also auch bestellte Produkte betrifft, erfordert eine zeilenweise Umschlüsselung zwischen customer_id und order_id durch die Orders-Tabelle.
Diese Bild ändert sich, wenn die Orders-Tabelle eine strukturierten Primärschlüssel aus customer_id und placed verwendet:
Dieses Schema wahrt den Zusammenhalt aller Zeilen, die zum selben Kunden gehören – über alle Tabellen hinweg. Das ist für Performance und Konsistenz von enormem Wert.
Ist das nicht Denormalisierung?
Es stimmt zwar, dass man mit Denormalisierung potentiell3 dieselben Performance-Vorteile erreichen könnte, es ist jedoch wichtig festzuhalten, dass ein strukturierter Primärschlüssel der Normalisierung nicht entgegensteht. Das folgende Diagramm zeigt ein Schema, dass der Performance wegen die zweite Normalform verletzt.
Dieses Schema bringt für die Suche nach dem letzten Mal, das ein Kunde ein Produkt bestellt hat, denselben Vorteil wie das Schema mit dem strukturierten Primärschlüssel. Dieses Schema stellt aber nicht sicher, dass die Customer_id-Werte in der Order_lines-Tabelle auf denselben Kunden zeigen wie die zugehörige Orders-Zeile. Früher oder später kommt es zu Datenfehlern. Viele glauben, dass die Applikation solche Probleme verhindern kann, über kurz oder lang gewinnt aber meist Murphys Gesetz.
id | customer_id |
|---|---|
| 1 | 10 |
| 2 | 20 |
order_id | customer_id |
|---|---|
| 1 | 20 |
| 2 | 20 |
| 2 | 10 |
Bie diesen Daten führt die Frage, welche Kunde die Bestellung 1 platziert hat, abhängig davon welche Tabelle man fragt, zu unterschiedlichen Antworten. Noch schlimmer ist sogar, dass sich die Order_lines-Tabelle bei der Frage nach dem Kunden zu Bestellung 2 selbst widerspricht.
Natürlich hätte ich gerne beides: Die Performance eines denormalisierten Schemas sowie die Konsistenz eines normalisierten. Genau das können strukturierte Primärschlüssel.
Speicher-Effizienz
Ich glaube, jetzt ist eine gute Gelegenheit den Platzbedarf von strukturierten Primärschlüssel zu beleuchten. Es ist absehbar, dass weitere Tabellen zu mehr Spalten in den Primärschlüssel führen. Das erhöht sowohl die Tipparbeit beim Schreiben der Abfragen aber auch die Wahrscheinlichkeit, in der On-Klausel einen Fehler zu machen. Das muss man so sagen wie es ist. Vorerst bleibt mir aber nichts anderes übrig, als diesen Nachteil zu akzeptieren – wohl wissentlich, dass manche SQL-Systeme einen Join ohne explizite Bedingung entlang eines Femdschlüssels durchführen können4 Generell sind Werkzeuge, die Abfragen aufgrund des Datenbankmodells automatisch erzeugen können, hier eine große Hilfe.
Ich möchte hier vor allem den Speicherverbraucht vom strukturieren Primärschlüsseln thematisieren. Grundsätzlich gibt es keine allgemein gültige Antwort auf die Frage, welches Primärschlüssel-Design mehr Platz braucht. Bei einem strukturierten Primärschlüssel fällt die Spalte oders.id zum Beispiel komplett weg. Dadurch erspart man sich oft auch einen Index.5 Andererseits erhält die Order_lines-Tabelle mehr Spalten. Bei Systemen, die Heap-Tabellen nutzen, zählt das gleich doppelt: Einmal in der Heap-Tabelle und ein zweites Mal im Index, der den Primärschlüssel unterstützt. Welcher Faktor überwiegt – Ersparnis in der einen Tabelle, Mehrverbrauch in der anderen – hängt natürlich auch von der Datenverteilung ab. Wenn jede Bestellung nur eine Order_lines-Zeile umfasst, kann die Ersparnis überwiegen. Wie gesagt: Es gibt hier keine allgemeingültige Antwort. Es gibt aber allgemein gültige Maßnahmen, die die Verhältnismäßigkeiten zugunsten strukturierter Primärschlüssel verschieben.
Wenn du diese Seite magst, magst du vielleicht auch …
… meine Newsletter abonnieren, gratis Sticker bestellen, mein Buch kaufen oder an einer Schulung teilnehmen.
Im Shop-Schema ist der Platzverbrauch des Primärschlüssel der Orders-Tabelle kritisch, weil seine Werte in alle Tabellen kopiert werden müssen, die sich über Fremdschlüssel auf die Order-Tabelle beziehen. Daher muss der Platzverbrauch dieses Primärschlüssel optimiert werden – ohne seine Struktur zu verlieren. Das ist ein weiteres Argument gegen die Aufnahme der Placed-Spalte in den Primärschlüssel. Schließlich fällt der Platzbedarf einer timestamp Spalte in den Bereich von 5 bis 11 Bytes – abhängig vom System und der Anzahl der Nachkommerstellen der Sekunden.
Deswegen schlage ich in solchen Fällen vor, eine Stellvertreterspalte einzuführen. Wichtig ist, dass der Primärschlüssel weiterhin zwei Spalten nutzt: Customer_id sowie placed, oder eben einen Stellvertreter dafür. Im Folgenden verwende ich eine Pro-Kunden Bestellnummer (order_no) als Stellvertreter. Solange es nur um die Eindeutigkeit des Primärschlüssel geht, können die Werte der Stellvertreterspalte per Zufall gewählt werden. Manchmal kann es aber vorteilhaft sein, die Werte so zu wählen, dass Sortierreihenfolge der der vertretenen Spalte (placed) erhalten bleibt (Sortier-Erhaltend). Das ist zum Beispiel bei der Suche nach dem Zeitpunkt der letzten Bestellung eines Produktes eines Kunden praktisch. Wir werden aber noch sehen, dass der Vorteil der Sortier-Erhaltung nicht unbedingt groß sein muss.
Das Schöne an solchen Stellvertreterspalten ist, dass der Wertebereich oft eng gewählt werden kann. Wenn man zum Beispiel bei einem einspaltigen Primärschlüssel wie order.id oft zu einem Bigint greifen muss, können beim strukturieren Primärschlüssel (customer_id, order_no) vielleicht zwei Integer-Werte genügen. Wenn, wie es oft der Fall ist, ein Bigint doppelt so viele Bytes belegt wie zwei Integer-Werte, ist der Platzbedarf beider Tabellen identisch.6 Letztendlich braucht das Design mit strukturiertem Primärschlüssel dann aber weniger Platz, weil es mit weniger Indizes auskommt.7
In diesen Zusammenhang gebe ich noch zwei Denkanstöße zu UUIDs: (1) eine UUID benötigt soviel Platz wie vier Integer-Werte8; (2) wer sich über UUIDv7 freut, will vermutlich schon länger einen Zeitstempel in den Primärschlüssel aufnehmen.
Pro-Kunde Zähler
So mancher mag sich jetzt wundern, woher die eben eingeführten Order_no-Werte kommen sollen. Als kurze Randnotiz möchte ich die überraschende Effizienz der folgenden Insert-Anweisung zeigen. Sie sucht die nächste für den jeweiligen Kunden verfügbare order_id, führt das Insert damit durch und liefert den Primärschlüsselwert der neuen Zeile zurück zur Anwendung.9
INSERT INTO orders (customer_id, order_no, placed)
SELECT customer_id
, (SELECT COALESCE(MAX(order_no),0) + 1
FROM orders WHERE customer_id = t.customer_id)
, CURRENT_TIMESTAMP
FROM (VALUES (?)) t(customer_id)
RETURNING customer_id, order_noDie Anwendung kann die Primärschlüsselwerte dann für die Insert in die Order_lines-Tabelle nutzen. Der nächste Chart zeigt die Laufzeit einer Transaktion, die mit diesem Insert eine neue Bestellung samt einer zugehörigen Order_lines-Tabelle anlegt. Die Basis (100%) ist dieselbe Transaktion in einem Schema mit einspaltigem Primärschlüssel orders.order_id. Inklusive der Suche nach dem nächsten pro-Kunden-Zähler braucht die Transaktion ungefähr ⅓ länger. Wie auch beim Platzbedarf gibt es keine allgemeine Antwort auf die Frage ob bei einer konkreten Anwendung die positiven oder die negativen Performance-Effekte eines strukturierten Primärschlüssel überwiegen.
Natürlich kann diese Vergabe von Order_no-Werten bei Nebenläufigkeit zu Primärschlüsselverletzungen führen. Genauso wie zwei identische Placed-Zeitstempel für einen Kunden zu einem Fehler führen, wenn der Primärschlüssel (customer_id, placed) lautet. Eine automatische, langsame (sleep) und begrenzte „Versuch’s nochmal“-Schleife ist nötig. Das ist aber ohnehin die allgemeine Lösung zum Umgang mit durch Nebenläufigkeit verursachten Fehlern wie zum Beispiel Deadlocks.10 Die dafür nötige Idempotenz kann mit einer dezidierten Unique-Spalte sichergestellt werden. Da diese Spalte keine Bedeutung für Fremdschlüssel hat, ist der Platzbedarf weniger kritisch. Der benötigte Index verdoppelt den Platzbedarf allerdings immer noch, sodass kürzere Typen bei Tabellen mit sehr vielen (kurzen) Zeilen immer noch eine Überlegung wert sind.
Beachte, dass die Returning-Klausel nicht standard-SQL ist und auch nicht weitläufig unterstützt wird. Der SQL-Standard kennt dafür zwar eine noch mächtigere Funktion, T495, „Combined data change and retrieval“, die aber auch nicht weitläufig unterstützt wird✓✗. Wenn dein System weder diese Funktion noch äquivalente anbietet, benötigt man für die Beschaffung eines steigenden Order_no-Wertes eine zusätzliche Abfrage. Wenn man die Reihenfolge allerdings nicht erhalten muss, kann auch eine Applikationsseitige Zuweisung von Order_no-Werten (Pools, Zufall, …) gemacht werden, falls eine sehr hohe Pro-Kunden Parallelität erwartet wird.
Zum Vergleich zeigt der nächste Chart die Auswirkung eines zufällig gewählten Order_no-Wertes bei der Suche nach dem Zeitpunkt, wann ein Kunde ein bestimmtes Produkt das letzte Mal bestellt hat. Die Abfrage unterscheidet sich nur in der On-Klausel von jener, die man in einem Schema mit einspaltigem Primärschlüssel verwenden würde. Dennoch ist die Antwortzeit beim strukturierten Primärschlüssel nur ein Bruchteil. Ist das nicht ein Wunder?
Natürlich ist das kein Wunder. Mit dem strukturierten Primärschlüssel sind die notwendigen Daten in der Order_lines-Tabelle verfügbar. Die Abfrage braucht nur jene Zeilen aufnehmen, die sowohl zum Kunden als auch zum Produkt passen. Danach muss sie nur noch die zugehörigen Orders-Zeilen laden, um den größten Zeitstempel zu finden.
Wenn die Order_lines Tabelle jedoch nur die Order_Id, nicht aber die Custmer_Id enthält, muss die Abfrage alle Order_Lines-Zeilen für das Produkt laden. Unabhängig davon, zu welchem Kunden diese Zeilen gehören. Dann muss es die entsprechenden Orders-Zeilen holen, nur um zu sehen, dass die meisten von andere Kunden sind. Der strukturierte Primärschlüssel spart doppelt: Einmal, weil nur die relevanten Order_Lines-Zeilen geholt werden, und dann, weil die zugehörigen Oders-Zeilen anderer Kunden auch nicht geladen werden müssen.11
Beachte, dass diese Beschleunigung weder auf das Entfernen eines Joins, noch durch eine sortier-erhaltende Vergabe der Order_no-Werte zustande kommt. Die Abfrage ist mit einem strukturierten Primärschlüssel vielfach schneller, weil die Orders-Tabelle die Kunden nicht von den Produkten trennt. Der Geschwindigkeitsfaktor zwischen den beiden Primärschlüssel Designs multipliziert sich prinzipiell mit jeder durchquerten Umschlüsselungstabelle12. Damit meine ich Tabellen, die in der linken und rechten Join-Bedingung unterschiedlichen Spalten verwenden. Beim Durchqueren zweier solcher Tabellen ist der Unterschied also nicht mehr Faktor vier bis zehn wie oben, sondern eher 16 bis 100. Und so weiter.
Zyklische Konsistenz
Ein weiter Vorteil eines Schemas mit strukturierten Primärschlüsseln ist, dass es vor Widersprüchen schützen kann, wenn es zwischen zwei Tabellen mehrere Join-Pfade gibt. Zur Verdeutlichung füge ich meiner Shop-Datenbank noch eine letzte Tabelle für Adressen hinzu. Um eine Liefer- und eine Rechnungsadresse abzubilden, füge ich der Orders-Tabelle zwei Spalten hinzu: ship_to und bill_to.
Der Primärschlüssel der Addresses-Tabelle ist wie die anderen strukturiert, wobei die Spalte address_no ein Stellvertreter für die eigentlich zur Eindeutigkeit notwendigen Spalten ist. Der hervorgehobene Fremdschlüssel zwischen den Tabellen Orders und Addresses verdient besondere Beachtung: Er verwendet die ohnehin vorhandene Customer_id-Spalte zusammen mit der neuen Spalte Ship_to, um auf den Primärschlüssel der Tabelle Addresses zu verweisen. Dadurch ist sichergestellt, dass die Ship_to-Adresse einer Bestellung zum gleichen Kunden gehört wie die Bestellung selbst. Das ist bei einem Shop eine sinnvolle geschäftliche Anforderung. Analoges gilt für den nicht abgebildeten Fremdschlüssel für die Rechnungsadresse (bill_to).
Hätte die Addresses-Tabelle nur einen einspaltigen Primärschlüssel (addresses.id), könnte eine Bestellung auf jede Adresse, unabhängig vom Kunden, verweisen. Aus dem Blickwinkel der Orders-Tabelle könnte man über zwei verschiedene Wege – direkt oder über die Addressses-Tabelle – zu zwei verschiedenen Kunden gelangen. Die Fremdschlüssel würden das nicht verhindern. Nochmal Murphy: „Alles, was schiefgehen kann, wird schiefgehen.“
Diese Konsistenz ist optional. Wenn es gewünscht ist, kann man in der Orders-Tabelle zu den Ship_to und Bill_to-Spalten jeweils auch die dazugehörige Customer_id ablegen (ship_to_customer, bill_to_customer). Dadurch werden kundenübergreifende Verweise explizit erlaubt. Im Zweifel kann man diese Spalten vorsorglich anlegen, aber mit einem Check-Constraint sicherstellen, dass alle denselben Wert haben. Falls sich die Annahme, dass Kundenübergreifende Verweise nicht zulässig sind, später als falsch herausstellt, muss man lediglich den Check-Constraint entfernen.
Diese Überlegung macht beim Shop-Beispiel wenig Sinn. Bei Mehrmandantensystemen kann es jedoch vorteilhaft sein, wenn mandantenübergreifende Verweise gezielt erlauben oder unterbinden kann.
Das abschließende i-Tüpfelchen zu diesem Thema ist, dass das oben gezeigte Schema sehr platzsparend ist. Wenn gut 32 Tausend Adressen pro Kunde ausreichend sind, genügt für die Spalte address_no ein entsprechend kleiner Datentyp, der mit zwei Byte auskommt.13 Die beiden Spalten ship_to und bill_to belegen in der Oders-Tabelle dann insgesamt vier Byte. Wäre der Primärschlüssel eine einzige Zahl, würde man wohl mindestens Integer verwenden und damit in der Orders-Tabelle acht Byte für die zwei Adressreferenzen belegen. Darüber hinaus spart man sich auf der Addresses-Tabelle einen Index.
Fazit
Letztendlich geht es bei der Definition strukturierten Primärschlüsseln nur um die Idee, einen sinnvollen Fremdschlüssel als Basis heranzuziehen und um jene Spalten zu erweitern, die für die Eindeutigkeit notwendig sind. Ich nenne diese zusätzlichen Spalten die Plus-Spalten. Das Rezept lautet also: Fremdschlüssel plus Plus-Spalten. Die Plus-Spalten sollten meist, wenn nicht immer, über eine Stellvertreterspalte abgebildet werden. Im letzten Diagramm sind die Fremdschlüsselspalten mit „⇧“, und die Plus-Spalten mit „+“ markiert.
Besonders aufmerksamen Lesern ist vielleicht aufgefallen, dass ich den Primärschlüssel der Order_lines-Tabelle zuletzt geändert habe. Anstatt der Product_id wird im letzten Diagramm die Stellvertreterspalte postion im Primärschlüssel verwendet. Das hält die Tür für spätere Änderungen bei der Abbildung der Produkte offen. Wenn zum Beispiel Produkt-Varianten eingeführt werden – Farbe, Größe,… – ändert das nichts am Primärschlüssel der Order_lines-Tabelle.14 Letztendlich kommt das „verwende immer einen künstlichen Schlüssel“ nicht von ungefähr, nur dass es eben nur auf die Plus-Spalten angewendet werden sollte.
Ich bin mir nicht sicher, ob es einen systematischen Ansatz zur Identifikation sinnvoller Fremdschlüssel gibt. Offensichtlich ist nicht jeder Fremdschlüssel als Basis für einen Primärschlüssel geeignet. Es wäre zum Beispiel sehr unpassend, den Primärschlüssel der Orders-Spalte auf dem Fremdschlüssel zur Addresses-Tabelle aufzubauen. Ebenso wäre es unpassend, einen Fremdschlüssel von der Addresses-Tabelle zu einer mit ISO-3166-Ländercodes als Basis für den Primärschlüssel der Addresses-Tabelle zu verwenden. Der Beste Tipp, den ich an dieser Stelle habe, ist dieser: Nutze den Hausverstand. Wenn man die Idee und die Vorteile von strukturierten Primärschlüsseln einmal verstanden hat, erledigt der Hausverstand oft den Rest.15
