Daten in Tabellen, verbunden über Schlüssel

Eine Datenbank ist ein Programm, das Daten dauerhaft speichert und schnell wiederfindet. Eine relationale Datenbank legt alles in Tabellen ab. Jede Zeile ist ein Eintrag, zum Beispiel ein Kunde. Jede Spalte steht für eine Eigenschaft, zum Beispiel den Namen. In einer Spalte ist nur eine bestimmte Art von Daten erlaubt, etwa nur Zahlen oder nur Text.

Das erinnert an eine Tabelle in Excel. Der Unterschied: Die Datenbank prüft jede Eingabe und kennt die Verbindungen zwischen den Tabellen.

Ein Beispiel aus einem kleinen Online-Shop. Die erste Tabelle enthält die Kunden, die zweite ihre Bestellungen:

kunden
idnameort
1Muster GmbHKarlsruhe
2Beispiel AGPforzheim
bestellungen
idkunde_idartikelmenge
1011Akkuschrauber2
1021Bohrersatz5
1032Schutzbrille10
Primärschlüssel
Ein Wert, der jede Zeile eindeutig macht, ähnlich wie eine Ausweisnummer. In beiden Tabellen ist das die Spalte id.
Fremdschlüssel
Ein Verweis auf eine Zeile in einer anderen Tabelle. In der Tabelle bestellungen bedeutet kunde_id 1: Diese Bestellung gehört zur Muster GmbH. Die Datenbank kann verhindern, dass eine Bestellung zu einem Kunden gehört, den es gar nicht gibt.

Der Ort eines Kunden steht dadurch nur an einer einzigen Stelle. Zieht die Firma um, ändert sich genau eine Zeile und nicht jede einzelne Bestellung. Dieses Aufteilen ohne doppelte Daten heißt Normalisierung.

Den Aufbau der Tabellen legt man vorab fest. Diesen Bauplan nennt man Schema. Er sorgt für Ordnung, lässt sich aber später ändern. Eine neue Spalte dauert in aktuellen Versionen von PostgreSQL und MySQL meist nur Sekunden. Aufwendig wird es, wenn vorhandene Daten umgebaut werden müssen, während die Anwendung weiterläuft.

SQL: die Sprache für Abfragen

SQL steht für Structured Query Language, auf Deutsch etwa „strukturierte Abfragesprache“. Damit sagen Sie der Datenbank, welches Ergebnis Sie haben möchten. Wie sie es findet, entscheidet sie selbst. SQL ist genormt, jedes Datenbanksystem hat aber eigene Erweiterungen. Die Grundlagen gelten also überall. Fertige Abfragen laufen aber nicht immer ohne Änderung auf einem anderen System.

Diese Abfrage zeigt alle Bestellungen der Muster GmbH:

SELECT artikel, menge
FROM bestellungen
WHERE kunde_id = 1;

SELECT wählt die Spalten aus, FROM nennt die Tabelle, und WHERE filtert die Zeilen. Das Ergebnis sind zwei Zeilen: Akkuschrauber 2 und Bohrersatz 5.

Mit JOIN verbindet eine Abfrage mehrere Tabellen über ihre Schlüssel. Diese Abfrage sucht alle Bestellungen von Kunden aus Pforzheim:

SELECT k.name, b.artikel, b.menge
FROM bestellungen AS b
JOIN kunden AS k ON k.id = b.kunde_id
WHERE k.ort = 'Pforzheim';

Die Buchstaben b und k sind nur Kurznamen für die beiden Tabellen. Das Ergebnis ist eine Zeile: Beispiel AG, Schutzbrille, 10. Das zeigt eine große Stärke relationaler Datenbanken: Auch Fragen, an die beim Bau niemand gedacht hat, lassen sich später stellen.

Der Index: schneller lesen, etwas langsamer schreiben

Stellen Sie sich ein Telefonbuch vor, das nicht alphabetisch sortiert ist. Um einen Namen zu finden, müssten Sie jede Seite lesen. Genauso muss die Datenbank ohne Index jede Zeile prüfen. Ein Index wirkt wie die Sortierung im Telefonbuch: Die Datenbank springt direkt an die richtige Stelle.

CREATE INDEX idx_bestellungen_kunde
ON bestellungen (kunde_id);

Ein Index wird zusätzlich gespeichert und braucht Platz. Außerdem muss die Datenbank ihn bei jeder Änderung aktualisieren. Deshalb legt man ihn nur für Spalten an, nach denen oft gesucht wird oder über die Tabellen verbunden werden.

Fachlich genauer

MySQL legt für einen Fremdschlüssel automatisch einen Index an, PostgreSQL nicht. Wer von MySQL zu PostgreSQL wechselt, sollte das prüfen. Sonst können manche Abfragen und Löschvorgänge spürbar langsamer werden.

Transaktionen: mehrere Schritte als eine Einheit

Bei einer Bestellung passieren mindestens zwei Dinge: Der Lagerbestand sinkt, und die Bestellung wird gespeichert. Angenommen, der Server fällt genau dazwischen aus. Dann fehlt Ware im Bestand, aber es gibt keine Bestellung dazu.

Eine Transaktion verhindert das. Sie fasst mehrere Schritte zu einer Einheit zusammen. Am Ende gelten entweder alle Schritte oder keiner. BEGIN startet die Transaktion, COMMIT schließt sie ab und speichert alles:

BEGIN;
UPDATE lager
  SET bestand = bestand - 2
  WHERE artikel = 'Akkuschrauber';
INSERT INTO bestellungen
  (kunde_id, artikel, menge)
  VALUES (1, 'Akkuschrauber', 2);
COMMIT;
Wichtig

Ohne BEGIN und COMMIT zählt in den meisten Systemen jede Anweisung als eigene Transaktion. Die Datenbank kann nur zusammenhalten, was das Programm auch als zusammengehörig markiert.

Was eine Transaktion garantiert, fasst die Abkürzung ACID zusammen:

Atomicity (Atomarität): alles oder nichts
Entweder werden alle Schritte gespeichert oder keiner. Fällt der Server vor dem COMMIT aus, nimmt die Datenbank alle bisherigen Schritte wieder zurück. Das nennt man Rollback.
Consistency (Konsistenz): nur gültige Zustände
Regeln, die in der Datenbank hinterlegt sind, gelten immer. Ein Beispiel ist CHECK (bestand >= 0): Der Bestand darf nie unter null fallen. Verstößt eine Änderung dagegen, lehnt die Datenbank sie ab. Regeln, die nur im Programm stehen, kann die Datenbank nicht schützen.
Isolation: keine halbfertigen Zwischenstände
Oft laufen viele Transaktionen gleichzeitig. Normalerweise sieht keine die noch nicht bestätigten Änderungen der anderen. Wie stark sie voneinander abgeschirmt sind, legt die Isolationsstufe fest. Nur die strengste Stufe, Serializable, verhält sich so, als liefen alle Transaktionen nacheinander. Standard sind schwächere Stufen: Read Committed bei PostgreSQL und Repeatable Read bei MySQL.
Durability (Dauerhaftigkeit): bestätigt heißt gespeichert
Nach dem COMMIT übersteht die Änderung auch einen Absturz oder Stromausfall. Das setzt passende Einstellungen und funktionierende Datenträger voraus.
Wenn ein einzelner Schritt scheitert

Nicht jede Datenbank reagiert gleich. PostgreSQL bricht nach einem Fehler die ganze Transaktion ab. MySQL nimmt oft nur die fehlgeschlagene Anweisung zurück, und die Transaktion läuft weiter. Das Programm muss Fehler deshalb prüfen und die Transaktion dann mit ROLLBACK abbrechen.

ACID schützt nicht vor allem. Ein fehlerhaftes Programm kann falsche Daten ganz korrekt speichern. Gegen versehentliches Löschen oder kaputte Hardware helfen nur Backups, also Sicherungskopien. Testen Sie auch, ob sich die Daten daraus wirklich wiederherstellen lassen.

Fachlich genauer: MySQL

Transaktionen brauchen eine passende Speicher-Engine, also den Teil von MySQL, der die Daten ablegt. InnoDB kann das und ist seit Version 5.5 der Standard. Bei der älteren Engine MyISAM hat ROLLBACK keine Wirkung. CHECK-Regeln setzt MySQL erst seit Version 8.0.16 durch, davor wurden sie ignoriert.

Wann relational die richtige Wahl ist

  • Ihre Daten hängen klar zusammen, zum Beispiel Kunden, Aufträge, Rechnungen und Lagerbestände.
  • Mehrere Änderungen müssen gemeinsam klappen oder gemeinsam scheitern.
  • Sie wollen Ihre Daten flexibel auswerten, auch mit Fragen, die heute noch niemand kennt.

Bekannte Systeme sind PostgreSQL, MySQL, MariaDB, Microsoft SQL Server und SQLite. Für die meisten Geschäftsanwendungen ist eines davon ein sehr guter Ausgangspunkt.

Weiter in Teil 2

Nicht alle Daten passen gut in Tabellen. Der nächste Artikel zeigt die vier wichtigsten NoSQL-Typen und was sie garantieren.