Viele-zu-viele-Beziehungen

Der Shop möchte seine Spiele mit Schlagwörtern (Tags) versehen, wie „Mehrspieler“, „Familie“ oder „Story“, damit Kunden finden, was ihnen gefällt. Ein Spiel kann mehrere Schlagwörter haben, und ein Schlagwort gehört zu vielen Spielen. Das ist eine Viele-zu-viele-Beziehung, die du in der Lektion über Joins kennengelernt hast, und sie passt nicht in die vorhandenen Tabellen des Shops:

• Eine Spalte schlagwort_id in spiele kann nur ein Schlagwort pro Spiel aufnehmen.
• Eine Spalte schlagwoerter mit einer Liste, wie 'Mehrspieler, Familie', lässt sich mit nichts verbinden, die Datenbank kann sie nicht prüfen, und sie mit LIKE zu durchsuchen ist umständlich und findet falsche Treffer.

Die Antwort ist eine dritte Tabelle, eine Verbindungstabelle, mit einer Zeile für jedes Paar: Dieses Spiel hat dieses Schlagwort. Sie hat zwei Fremdschlüssel, einen auf jede Tabelle, und das Paar ist ihr Primärschlüssel. Da der Schlüssel zwei Spalten hat, wird er nach der letzten Spalte geschrieben: PRIMARY KEY (spiel_id, schlagwort_id). Jedes Spiel kann dann in vielen Zeilen vorkommen, und jedes Schlagwort auch, aber dasselbe Paar kann nicht zweimal vorkommen.

Solche Tabellen kennst du schon. positionen verbindet Bestellungen und Spiele (eine Bestellung hat viele Spiele, ein Spiel steckt in vielen Bestellungen), und bewertungen verbindet Spiele und Kunden. Beide haben einen Primärschlüssel aus zwei Spalten, und beide zeigen, dass eine Verbindungstabelle mehr über das Paar enthalten kann: die Menge und den Preis oder die Sterne und den Kommentar.

Hier dasselbe für die Plattformen, auf denen die Spiele laufen: eine Tabelle der Plattformen, eine Verbindungstabelle und eine Abfrage, die alle drei verbindet:

CREATE TABLE plattformen (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
) STRICT;

CREATE TABLE spiel_plattformen (
    spiel_id INTEGER NOT NULL REFERENCES spiele (id),
    plattform_id INTEGER NOT NULL REFERENCES plattformen (id),
    PRIMARY KEY (spiel_id, plattform_id)
) STRICT;

INSERT INTO plattformen (name)
VALUES ('PC'), ('Switch');

INSERT INTO spiel_plattformen (spiel_id, plattform_id)
VALUES (3, 1), (3, 2), (6, 1);

SELECT s.titel, p.name AS plattform
FROM spiel_plattformen sp
JOIN spiele s ON s.id = sp.spiel_id
JOIN plattformen p ON p.id = sp.plattform_id
ORDER BY s.titel, p.name;

Moon Farm läuft auf zwei Plattformen, also hat es zwei Zeilen in spiel_plattformen. Die Abfrage beginnt bei der Verbindungstabelle und verbindet jeden ihrer Fremdschlüssel mit seiner Tabelle, wie du es in der Lektion über Joins gelernt hast.

Typischer Fehler: PRIMARY KEY nach beiden Spalten zu schreiben. Das ergäbe zwei Primärschlüssel, und eine Tabelle kann nur einen haben:

CREATE TABLE spiel_plattformen (
    spiel_id INTEGER PRIMARY KEY,
    plattform_id INTEGER PRIMARY KEY
) STRICT;

Error on line 1: table "spiel_plattformen" has more than one primary key