Natürliche Schlüssel und Denormalisierung

Kurz gesagt: Ein natürlicher Schlüssel stammt aus den Daten selbst, etwa eine E-Mail, ein Ersatzschlüssel ist eine Zahl ohne Bedeutung, und Denormalisierung ist eine Kopie von Daten, die du mit Absicht behältst.

Problem

Maria hat ihre E-Mail geändert

Wenn du die Kunden selbst entwirfst, wirkt die E-Mail wie ein guter Primärschlüssel. Sie ist eindeutig, jeder Kunde hat eine, und er meldet sich damit an. Hier ist so ein Entwurf: Die Kunden haben die E-Mail als Schlüssel, und die Bestellungen zeigen auf die E-Mail.

customers

• PKemailtext
• nametext *
• citytext

orders

• PKidinteger
• FKcustomer_emailtext *→ customers
• ordered_attext *

Maria ändert ihre E-Mail:

SQLleere Datenbank

UPDATE customers SET email = 'maria.i@example.com'
WHERE email = 'maria.ivanova@example.com';

UPDATE customers SET email = 'maria.i@example.com' WHERE email = 'maria.ivanova@example.com';

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Die Datenbank lehnt die Änderung ab, weil drei Bestellungen auf die alte E-Mail zeigen. Wenn du auch die änderst, bleiben noch die schon verschickten Rechnungen, die Berichte und jedes andere System, das sich den alten Schlüssel gemerkt hat.

Die Idee

Natürliche und künstliche Schlüssel

Ein Schlüssel aus der Welt (eine E-Mail, eine Telefonnummer, die Nummer eines Buchs) ist ein natürlicher Schlüssel. Eine Zahl, die die Datenbank selbst vergibt und die außerhalb keine Bedeutung hat, ist ein Ersatzschlüssel (Surrogatschlüssel). Die id im Shop ist einer.

Ein Ersatzschlüssel ändert sich nie, weil ihn nichts in der Welt ändert. Die E-Mail ändert sich also in einer Zeile, und die Bestellungen merken nichts:

SQLDatenbank: Shop

UPDATE customers SET email = 'maria.i@example.com' WHERE id = 1;

SELECT c.name, c.email, COUNT(*) AS orders
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 1
GROUP BY c.id, c.name, c.email;

UPDATE customers SET email = 'maria.i@example.com' WHERE id = 1; SELECT c.name, c.email, COUNT(*) AS orders FROM customers c JOIN orders o ON o.customer_id = c.id WHERE c.id = 1 GROUP BY c.id, c.name, c.email;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Die E-Mail behält ihre Regel. Sie ist kein Schlüssel mehr, aber UNIQUE, sodass zwei Kunden nicht dieselbe E-Mail haben können:

SQLDatenbank: Shop

UPDATE customers SET email = 'georgi.petrov@example.com' WHERE id = 1;

UPDATE customers SET email = 'georgi.petrov@example.com' WHERE id = 1;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Und eine nationale Ausweisnummer (in Bulgarien die EGN)? Nein. Sie sind personenbezogene Daten, und der Schlüssel wird in jede Tabelle kopiert, die auf den Kunden zeigt. Ein Kunde aus dem Ausland hat meist keine. Und der Shop braucht sie nicht zu wissen.

Genauer, für Neugierige

ON UPDATE CASCADE in REFERENCES sorgt dafür, dass die Datenbank auch die Zeilen ändert, die auf den alten Schlüssel zeigen. Das hilft innerhalb der Datenbank, aber nicht bei den Kopien des Schlüssels außerhalb: in E-Mails, in Logs, in einem anderen System.

Ein natürlicher Schlüssel passt, wenn der Wert sich wirklich nie ändert und von außen bekannt ist. Beispiele sind ein Währungscode (EUR) oder ein Ländercode (BG).

Eine UUID ist eine andere Art Ersatzschlüssel: eine 128-Bit-Zahl, geschrieben als 550e8400-e29b-41d4-a716-446655440000. Sie lässt sich überall erzeugen, ohne die Datenbank zu fragen (in der App, auf einem Handy ohne Internet), zwei Datenbanken lassen sich ohne Konflikte zusammenführen, und die Adresse /orders/1043 verrät nicht mehr, wie viele Bestellungen der Shop hat. Der Preis ist, dass sie länger ist (16 Byte gegen 8 bei einer großen Ganzzahl) und zufällige UUIDs an zufälligen Stellen im Index landen und ihn verlangsamen. Deshalb gibt es auch zeitlich geordnete UUIDs (Version 7). PostgreSQL hat einen Typ uuid und eine Funktion gen_random_uuid(). SQLite hat keinen eigenen Typ und speichert sie als Text oder als BLOB.

Probier es aus

Eine neue E-Mail

Kunde 12, Boris, schreibt, dass seine neue E-Mail boris.m@example.com ist. Korrigiere sie.

SQLDatenbank: Shop

Prüfen▶ Ausführen

Die Idee

Der Preis in der Bestellzeile

order_items hat unit_price, und products hat price. Das sieht aus wie eine Wiederholung, die die Normalisierung entfernt. Erhöhe den Preis des Kaffees:

SQLDatenbank: Shop

UPDATE products SET price = 15.9 WHERE id = 17;

SELECT oi.order_id, oi.unit_price, p.price AS today
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.product_id = 17;

UPDATE products SET price = 15.9 WHERE id = 17; SELECT oi.order_id, oi.unit_price, p.price AS today FROM order_items oi JOIN products p ON p.id = oi.product_id WHERE oi.product_id = 17;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Die alten Bestellungen merken sich 14,90 €, während das Produkt 15,90 € kostet. Das sind nicht zwei Kopien einer Tatsache, sondern zwei verschiedene Tatsachen: „was es heute kostet“ und „was der Kunde bezahlt hat“. Würde die Bestellung ihren Preis aus products nehmen, würde jede Preiserhöhung auch vergangene Rechnungen ändern.

1. Der Kaffee in products kostet jetzt 15,90 €. Was soll die alte Bestellung 8 zeigen?

Das hängt davon ab, wann der Bericht geöffnet wird14,90 €, weil das der Preis war, zu dem gekauft wurde15,90 €, weil das der Preis heute ist

Prüfen

Die Idee

Denormalisierung

Die Summe der Bestellung ist anders. Sie wird wirklich aus den Positionen berechnet. Aber die Bestellliste auf der Website zeigt sie für jede Bestellung, und statt jedes Mal die Positionen zu addieren, kannst du sie in orders behalten:

SQLDatenbank: Shop

ALTER TABLE orders ADD COLUMN total REAL;

UPDATE orders SET total = (
  SELECT SUM(quantity * unit_price) FROM order_items
  WHERE order_items.order_id = orders.id
);

SELECT id, total FROM orders WHERE id >= 58;

ALTER TABLE orders ADD COLUMN total REAL; UPDATE orders SET total = ( SELECT SUM(quantity * unit_price) FROM order_items WHERE order_items.order_id = orders.id ); SELECT id, total FROM orders WHERE id >= 58;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Eine bewusste Kopie von Daten, die sich berechnen ließen, heißt Denormalisierung. Du machst sie, wenn Lesen viel häufiger ist als Ändern und die Berechnung teuer ist.

Sieh es dir an

Die Summe, die lügt

Ein Kollege fügt der Bestellung 60 Kaffee hinzu, vergisst aber total:

SQLDatenbank: Shop

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (60, 17, 1, 14.9);

SELECT o.id, o.total, SUM(oi.quantity * oi.unit_price) AS items
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.id = 60
GROUP BY o.id, o.total;

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (60, 17, 1, 14.9); SELECT o.id, o.total, SUM(oi.quantity * oi.unit_price) AS items FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.id = 60 GROUP BY o.id, o.total;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Die Website zeigt 14 €, während die Positionen 28,90 € ergeben. Nichts in der Datenbank hat das verhindert. Der Preis der Kopie ist: Jede Änderung an den Positionen muss auch die Summe ändern, im Programm, an jeder Stelle, die in order_items schreibt, oder mit einem Trigger (Modul „Transaktionen“).

Übung

Zwei Aufgaben

1. Die Buchhaltung vermutet, dass manche Summen falsch sind. Finde die Bestellungen, bei denen total nicht gleich der Summe der Positionen ist. Zeige die Nummer, die gespeicherte Summe und die echte.

Die Bestellungen mit falscher Summe:

SQLDatenbank: Shop

Prüfen▶ Ausführen

2. Korrigiere die Summen der beiden Bestellungen, indem du sie aus den Positionen neu berechnest.

Aktualisiere total für die Bestellungen 59 und 60:

SQLDatenbank: Shop

Prüfen▶ Ausführen

Test

Teste dich

1. Welcher ist ein Ersatzschlüssel (Surrogatschlüssel)?

Eine nationale Ausweisnummerid INTEGER PRIMARY KEY, das die Datenbank selbst vergibtDie E-Mail des Kunden

2. Der Shop benutzt id als Schlüssel. Soll die E-Mail trotzdem UNIQUE sein?

Nein, es gibt ja schon einen SchlüsselNur wenn sie NOT NULL istJa, sonst könnten zwei Kunden dieselbe E-Mail haben

3. Warum verletzt unit_price in order_items die Normalisierung nicht?

Weil es eine andere Tatsache ist: der Preis am Tag des Kaufs, nicht der heutigeSie verletzt sie, aber das ist erlaubtWeil es eine Zahl ist

4. Eine Spalte total in orders speichert die Summe der Positionen. Was kostet das?

Bestellungen werden schreibgeschütztJede Änderung an den Positionen muss auch total ändern, sonst stimmt die Summe nichtNichts, es macht nur alles schneller

Prüfen

Lektion von vorn beginnen

Verwandte Lektionen

• Baut auf: Fremdschlüssel: Verbindungen zwischen Tabellen · ALTER TABLE: eine Tabelle ändern · Beziehungstypen: 1:1, 1:N, M:N · Normalisierung
• Geht weiter in: OLTP und OLAP · Dokumentendatenbanken · Spaltenorientierte Datenbanken und Zeitreihen · Die Bäckerei, Etappe 1: Datenbank und Tabellen
• Aufgaben zum Thema: Eine Datenbank entwerfen