LU08e: N:M-Beziehungen mit Zwischentabelle abbilden

Ziel: Sie modellieren eine Many-to-Many-Beziehung (Posts ↔ Kategorien) korrekt mit einer Zwischentabelle post_category und füllen Daten-Zuordnungen.

Voraussetzung: Die Tabellen und Beispieldaten aus LU08c (users, posts, categories) sind vorhanden. (Die Verknüpfung users ↔ posts ist 1:N; posts ↔ categories ist N:M.)

In einer relationalen Tabelle zeigt ein Fremdschlüssel immer nur auf genau eine Zeile. Bei N:M gilt jedoch: ein Post → mehrere Kategorien und eine Kategorie → mehrere Posts. Das lässt sich nicht mit einem einzigen FK-Feld abbilden:

1. FK in posts nur als category_id: Pro Post wäre nur eine Kategorie möglich. Mehrere Kategorien erzwingen Duplikate desselben Posts – Änderungen und Löschungen werden fehleranfällig.
2. FK in categories nur als post_id: Pro Kategorie wäre nur ein Post möglich. Dadurch müsste man Kategorien duplizieren – gleiches Problem wie bei obigen Punkt.
3. Kommaliste in einer Spalte (z. B. Belgien, Städtereise): In einer Zelle stehen mehrere Werte. Die Datenbank kann nicht prüfen, ob diese Kategorien existieren (kein FK), Abfragen werden unzuverlässig und langsam.
4. mehrere FK-Spalten (z.B. category_id1, category_id2 etc.) pro Post oder Kategorie: Das limitiert die Anzahl mögliche Kategorien, die einem Post zugewiesen können (limitiert die Anzahl an Beziehungen, da bei der Tabellendefinition die Spalten definiert werden müssen.

Gleich hier ein paar Tabellen, die die einzelnen vorhin aufgeführten Punkte visualisieren alles sind schlechte Lösungen (s. obige Punkte):

Lösung: die Zwischentabelle post_category. Wir trennen die Objekte (posts, categories) und speichern jede einzelne Zuordnung als eigene Zeile in post_category – genau ein Paar (post_id, category_id) pro Zeile. So bleibt jede Beziehung eindeutig modelliert, Primärschlüssel bleiben eindeutig, Fremdschlüssel sind prüfbar, und Abfragen bleiben klar und performant.

users ↔ posts (1:N)

posts ↔ categories (N:M) – korrekt über Zwischentabelle post_category

Eine Zeile = genau eine Zuordnung (post_id, category_id). CASCADE sorgt dafür, dass Zuordnungen automatisch gelöscht werden, wenn ein Post oder eine Kategorie gelöscht wird.

CREATE TABLE post_category (
post_id     INT NOT NULL,
category_id INT NOT NULL,
PRIMARY KEY (post_id, category_id),
FOREIGN KEY (post_id) REFERENCES posts (post_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (category_id) REFERENCES categories (category_id) ON DELETE CASCADE ON UPDATE CASCADE
);

(IDs gemäss LU08c)

INSERT INTO post_category (post_id, category_id) VALUES
(1, 4),  -- Hasselt → Belgien
(1, 1),  -- Hasselt → Städtereise
 
(2, 5),  -- Utrecht → Niederlande
(2, 1),  -- Utrecht → Städtereise
 
(3, 6),  -- Lissabon → Portugal
(3, 1),  -- Lissabon → Städtereise
 
(4, 5),  -- Maastricht → Niederlande
(4, 1),  -- Maastricht → Städtereise
 
(5, 8),  -- Montenegro Roadtrip → Montenegro
(5, 2),  -- Montenegro Roadtrip → Abenteuer
(5, 3),  -- Montenegro Roadtrip → Roadtrip
 
(6, 9),  -- Oman → Oman
(6, 2),  -- Oman → Abenteuer
(6, 3),  -- Oman → Roadtrip
 
(7, 10), -- Chicago → USA
(7, 1);  -- Chicago → Städtereise 

Beim Löschen eines Posts (oder einer Kategorie) werden die Zuordnungen in post_category automatisch entfernt.

-- Demo: Post #2 löschen
DELETE FROM posts WHERE post_id = 2;
 
-- Kontrolle: Mappings zu Post #2 sind weg
SELECT * FROM post_category WHERE post_id = 2; 

Erwartung: Die letzte Abfrage liefert keine Zeilen (CASCADE hat die Zuordnungen bereinigt).

• Aufgabe A: Fügen Sie eine neue Kategorie `Benelux` (slug: benelux) ein und ordnen Sie Hasselt – 10 Highlights zusätzlich dieser Kategorie zu. Prüfen Sie danach, ob drei Kategorien für den Post angezeigt werden.
• Aufgabe B: Löschen Sie die Kategorie roadtrip. Prüfen Sie, dass die Posts weiterhin existieren und nur die entsprechenden post_category-Zeilen verschwunden sind (CASCADE).

* N:M wird immer über eine Zwischentabelle mit einer Paarung pro Zeile modelliert: (post_id, category_id). * Fremdschlüssel sichern die Datenqualität; mit ON DELETE CASCADE werden Zuordnungen automatisch aufgeräumt.