Many-to-Many-Beziehungen richtig modellieren

1. Warum Many-to-Many nicht direkt abbildbar ist

Eine Many-to-Many-Beziehung beschreibt einen Zusammenhang, bei dem ein Datensatz der einen Tabelle mit beliebig vielen Datensätzen einer anderen Tabelle verbunden sein kann, und umgekehrt. Ein Autor schreibt mehrere Bücher, ein Buch hat mehrere Autoren. Ein Student belegt mehrere Kurse, ein Kurs hat mehrere Studenten. Das relationale Modell erlaubt in einer einzelnen Zeile jedoch nur eine feste Anzahl von Spaltenwerten, keine variable Liste von Fremdschlüsseln. Ein Fremdschlüssel in der Tabelle "Buecher", der direkt auf mehrere Zeilen der Tabelle "Autoren" verweist, lässt sich in einer einzigen Spalte nicht abbilden, ohne gegen die erste Normalform zu verstoßen.

Der naive Versuch, mehrere Autoren-IDs kommasepariert in einer Textspalte zu speichern, funktioniert zwar technisch, zerstört aber jede Möglichkeit, effizient zu filtern, zu joinen oder referenzielle Integrität durchzusetzen. Eine Abfrage nach allen Büchern eines bestimmten Autors würde auf einen Textmuster-Vergleich mit LIKE hinauslaufen, was weder einen Index nutzen kann noch vor Tippfehlern schützt. Die Many-to-Many-Beziehung braucht deshalb eine eigene Struktur, die das Problem sauber auf das relationale Modell abbildet: eine dritte Tabelle, die zwischen den beiden Haupttabellen vermittelt.

Dieses Muster ist keine Krücke, sondern die einzige Lösung, die referenzielle Integrität, Indizierbarkeit und Erweiterbarkeit gleichzeitig ermöglicht. Wer eine Many-to-Many-Beziehung von Anfang an korrekt modelliert, erspart sich spätere Migrationen, bei denen Millionen Zeilen aus einer Textspalte in eine normalisierte Struktur überführt werden müssen. Die folgenden Abschnitte zeigen den vollständigen Aufbau, von der einfachen Verbindungstabelle bis zu Beziehungen mit eigenen Attributen.

2. Die Junction-Tabelle als Standardlösung

Die Standardlösung für jede Many-to-Many-Beziehung ist eine sogenannte Junction-Tabelle, auch Assoziativtabelle oder Verbindungstabelle genannt. Sie enthält mindestens zwei Fremdschlüsselspalten, eine pro beteiligter Haupttabelle, und stellt damit jede einzelne Verbindung als eigene Zeile dar. Statt einer Liste von Autoren-IDs in der Buch-Tabelle entsteht eine Zeile pro Autor-Buch-Kombination in der Tabelle "buch_autor". Ein Buch mit drei Autoren erzeugt drei Zeilen, ein Autor mit fünf Büchern erzeugt fünf Zeilen. Diese Zerlegung ist der Kern jeder korrekten Modellierung einer Many-to-Many-Beziehung im relationalen Modell.

Aus Sicht der beiden Haupttabellen wird die ursprüngliche Many-to-Many-Beziehung dadurch in zwei One-to-Many-Beziehungen aufgelöst: Ein Autor hat viele Einträge in der Junction-Tabelle, ein Buch hat viele Einträge in der Junction-Tabelle. Diese Auflösung ist genau der Grund, warum die Junction-Tabelle funktioniert, denn One-to-Many-Beziehungen lassen sich im relationalen Modell problemlos über einen einfachen Fremdschlüssel abbilden. Die Junction-Tabelle selbst braucht in ihrer einfachsten Form keine eigene fachliche Bedeutung, sie ist reine Infrastruktur für die Beziehung.

-- Two main tables, connected via a junction table
CREATE TABLE author (
    author_id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(150) NOT NULL
);

CREATE TABLE book (
    book_id     INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    title       VARCHAR(255) NOT NULL,
    published   DATE NOT NULL
);

-- Junction table resolving the many-to-many relationship
CREATE TABLE book_author (
    book_id     INT UNSIGNED NOT NULL,
    author_id   INT UNSIGNED NOT NULL,
    PRIMARY KEY (book_id, author_id),
    CONSTRAINT fk_book_author_book
        FOREIGN KEY (book_id) REFERENCES book (book_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_book_author_author
        FOREIGN KEY (author_id) REFERENCES author (author_id)
        ON DELETE CASCADE
);

3. Composite Primary Key vs. Surrogate Key

Bei der Junction-Tabelle stellt sich sofort die Frage nach dem Primärschlüssel. Die naheliegende Lösung ist ein zusammengesetzter Primärschlüssel aus beiden Fremdschlüsselspalten, wie im vorherigen Beispiel gezeigt. Diese Variante hat einen entscheidenden Vorteil: Die Kombination aus book_id und author_id ist automatisch eindeutig, ein zusätzlicher UNIQUE-Constraint erübrigt sich. Der zusammengesetzte Schlüssel drückt fachlich exakt aus, was die Tabelle repräsentiert, nämlich genau eine Verbindung zwischen einem bestimmten Buch und einem bestimmten Autor, die nicht doppelt existieren darf.

Die Alternative ist ein künstlicher Surrogate Key, meist eine eigene AUTO_INCREMENT-Spalte, kombiniert mit einem separaten UNIQUE-Constraint auf beide Fremdschlüsselspalten. Diese Variante lohnt sich, sobald die Junction-Tabelle selbst wieder Ziel von Fremdschlüsseln aus anderen Tabellen wird, etwa wenn eine dritte Tabelle sich auf genau eine bestimmte Buch-Autor-Zuordnung bezieht. Ein einzelner Integer-Fremdschlüssel ist einfacher zu referenzieren als ein zusammengesetzter, und ORM-Frameworks kommen mit einer einzelnen ID-Spalte in der Regel besser zurecht als mit Composite Keys.

Als Faustregel gilt: Bleibt die Many-to-Many-Beziehung ein reines Verbindungsglied ohne eigene Identität, ist der Composite Primary Key die schlankere und klarere Lösung. Wird die Beziehung selbst zu einer fachlichen Entität mit eigenem Lebenszyklus, etwa eine Bestellposition mit Menge und Preis, überwiegen die Vorteile eines Surrogate Keys mit begleitendem UNIQUE-Constraint.

-- Variant A: composite primary key (no extra identity needed)
CREATE TABLE book_author (
    book_id     INT UNSIGNED NOT NULL,
    author_id   INT UNSIGNED NOT NULL,
    PRIMARY KEY (book_id, author_id)
);

-- Variant B: surrogate key, useful when other tables reference
-- a single relationship row directly
CREATE TABLE book_author (
    book_author_id  BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id         INT UNSIGNED NOT NULL,
    author_id       INT UNSIGNED NOT NULL,
    CONSTRAINT uq_book_author UNIQUE (book_id, author_id)
);

4. Attribute an der Beziehung selbst speichern

Eine Many-to-Many-Beziehung bleibt oft nicht bei zwei nackten Fremdschlüsseln, sondern trägt eigene Eigenschaften. Bei "buch_autor" könnte das die Reihenfolge der Autorennennung auf dem Cover sein, bei einer Bestellung ist es die Menge und der zum Zeitpunkt der Bestellung gültige Preis eines Artikels. Genau dieses zweite Beispiel zeigt, warum die Junction-Tabelle mehr ist als reine Infrastruktur: Die Tabelle "order_item" verbindet "orders" und "product", trägt aber zusätzlich quantity und unit_price, die fachlich weder zur Bestellung noch zum Produkt gehören, sondern ausschließlich zur Beziehung zwischen beiden.

Diese zusätzlichen Spalten sind der Grund, warum Junction-Tabellen fast immer einen eigenen Namen bekommen, der über eine reine Kombination der beiden Haupttabellen hinausgeht: "order_item" statt "orders_product". Der Name signalisiert, dass die Tabelle eine eigenständige fachliche Bedeutung trägt. Für den Preis gilt dabei eine wichtige Regel: Er wird zum Zeitpunkt der Bestellung in die Junction-Tabelle kopiert und nicht zur Laufzeit aus der Produkttabelle nachgeladen, weil sich Produktpreise ändern, historische Bestellungen aber unverändert bleiben müssen.

-- Junction table carrying attributes of the relationship itself
CREATE TABLE order_item (
    order_item_id   BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    order_id        BIGINT UNSIGNED NOT NULL,
    product_id      INT UNSIGNED NOT NULL,
    quantity        INT NOT NULL CHECK (quantity > 0),
    unit_price      DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
    CONSTRAINT fk_order_item_order
        FOREIGN KEY (order_id) REFERENCES orders (order_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_order_item_product
        FOREIGN KEY (product_id) REFERENCES product (product_id)
        ON DELETE RESTRICT,
    CONSTRAINT uq_order_item UNIQUE (order_id, product_id)
);

5. Fremdschlüssel, ON DELETE und referenzielle Integrität

Jede Fremdschlüsselspalte einer Junction-Tabelle sollte eine explizite ON DELETE-Regel tragen, statt sich auf das Standardverhalten zu verlassen. ON DELETE CASCADE entfernt bei Löschung des übergeordneten Datensatzes automatisch alle zugehörigen Zeilen in der Junction-Tabelle. Für die Beziehung zwischen Buch und Autor ist das sinnvoll: Wird ein Buch komplett aus dem System entfernt, ergeben verwaiste Einträge in "buch_autor" keinen Sinn mehr. Bei "order_item" sieht die Lage anders aus, weil Bestellungen aus rechtlichen Gründen oft nicht gelöscht werden dürfen, daher wird dort typischerweise ON DELETE RESTRICT für den Produkt-Fremdschlüssel verwendet, um ein versehentliches Löschen referenzierter Produkte zu verhindern.

Ohne Fremdschlüssel-Constraints kann die Datenbank die referenzielle Integrität einer Many-to-Many-Beziehung nicht garantieren, selbst wenn die Anwendungslogik korrekt implementiert ist. Ein fehlgeschlagener Batch-Job, ein manuelles Datenbank-Skript oder ein Bug in einer anderen Anwendung, die dieselbe Datenbank nutzt, kann sonst verwaiste Junction-Zeilen erzeugen, die auf nicht mehr existierende Bücher oder Autoren verweisen. Diese Inkonsistenzen fallen oft erst Monate später auf, wenn ein JOIN plötzlich NULL-Werte liefert, wo eigentlich ein gültiger Datensatz erwartet wird.

6. Indizierung von Junction-Tabellen für Performance

Ein Composite Primary Key auf (book_id, author_id) erzeugt automatisch einen zusammengesetzten Index, der Abfragen nach book_id effizient bedient, weil book_id die führende Spalte des Index ist. Abfragen nach author_id allein profitieren von diesem Index jedoch kaum, weil author_id nicht am Anfang steht. Für eine Many-to-Many-Beziehung, die in beide Richtungen häufig abgefragt wird, etwa "alle Bücher eines Autors" und "alle Autoren eines Buchs", braucht es deshalb einen zweiten Index, der mit author_id beginnt.

Bei Junction-Tabellen mit Surrogate Key ist dieser zweite Index besonders wichtig, weil der UNIQUE-Constraint zwar Eindeutigkeit sicherstellt, aber je nach Spaltenreihenfolge nur eine der beiden Suchrichtungen effizient unterstützt. Ein zusätzlicher Index auf die jeweils zweite Fremdschlüsselspalte ist in der Praxis fast immer notwendig, sobald die Tabelle über einige Zehntausend Zeilen hinauswächst. Der Speicherplatz für einen zweiten Index ist gering im Vergleich zu den Kosten eines Full-Table-Scans bei jeder Abfrage in die andere Richtung.

7. Abfragen: JOIN-Strategien für Many-to-Many

Der Standardweg, eine Many-to-Many-Beziehung abzufragen, führt über zwei INNER JOINs: einmal von der Ausgangstabelle zur Junction-Tabelle, einmal von der Junction-Tabelle zur Zieltabelle. Diese doppelte JOIN-Kette ist notwendig, weil zwischen den beiden Haupttabellen keine direkte Fremdschlüsselbeziehung existiert. Wichtig ist, sich klarzumachen, dass jede Zeile im Ergebnis einer Kombination aus Buch und Autor entspricht, nicht einer Zeile pro Buch. Ein Buch mit drei Autoren erscheint dementsprechend dreimal im Ergebnis, sofern keine Aggregation stattfindet.

Für Fragen wie "wie viele Autoren hat dieses Buch" oder "liste alle Autorennamen kommasepariert pro Buch" kommen Aggregatfunktionen wie COUNT und GROUP_CONCAT beziehungsweise STRING_AGG ins Spiel. Für Existenzprüfungen, etwa "gibt es mindestens einen gemeinsamen Autor zwischen zwei Büchern", ist EXISTS oft performanter als ein zusätzlicher JOIN, weil die Datenbank die Suche beim ersten Treffer abbrechen kann, statt alle Kombinationen zu materialisieren.

-- Composite primary key covers lookups by book_id efficiently
-- but a second index is required for the reverse direction
CREATE INDEX idx_book_author_author
    ON book_author (author_id);

-- Standard two-hop join across the junction table
SELECT b.title, a.name
FROM book b
JOIN book_author ba ON ba.book_id = b.book_id
JOIN author a ON a.author_id = ba.author_id
WHERE b.book_id = 17;

-- Aggregate: count authors per book
SELECT b.title, COUNT(ba.author_id) AS author_count
FROM book b
LEFT JOIN book_author ba ON ba.book_id = b.book_id
GROUP BY b.book_id, b.title;

-- Existence check without materializing all combinations
SELECT EXISTS (
    SELECT 1
    FROM book_author ba1
    JOIN book_author ba2 ON ba2.author_id = ba1.author_id
    WHERE ba1.book_id = 17 AND ba2.book_id = 23
) AS shared_author;

8. Self-Referencing Many-to-Many-Beziehungen

Eine Many-to-Many-Beziehung muss nicht zwischen zwei unterschiedlichen Tabellen bestehen, sie kann auch innerhalb einer einzigen Tabelle auftreten. Das klassische Beispiel ist "verwandte Produkte" in einem Onlineshop: Ein Produkt kann mit beliebig vielen anderen Produkten derselben Tabelle verknüpft sein. Die Junction-Tabelle referenziert dabei zweimal dieselbe Haupttabelle, mit zwei unterschiedlich benannten Fremdschlüsselspalten wie product_id und related_product_id.

Bei symmetrischen Beziehungen, bei denen "A verwandt mit B" automatisch "B verwandt mit A" bedeutet, stellt sich die Frage, ob beide Richtungen als eigene Zeile gespeichert werden oder ob die Anwendung die Symmetrie zur Abfragezeit herstellt. Werden beide Richtungen gespeichert, verdoppelt sich die Zeilenzahl, aber jede Abfrage bleibt ein einfacher Filter auf eine Spalte. Wird nur eine Richtung gespeichert, muss jede Abfrage beide Spalten mit OR prüfen oder die kleinere ID immer in die erste Spalte schreiben, um Duplikate von vornherein zu vermeiden.

-- Self-referencing many-to-many: related products
CREATE TABLE related_product (
    product_id          INT UNSIGNED NOT NULL,
    related_product_id  INT UNSIGNED NOT NULL,
    PRIMARY KEY (product_id, related_product_id),
    CHECK (product_id <> related_product_id),
    CONSTRAINT fk_related_source
        FOREIGN KEY (product_id) REFERENCES product (product_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_related_target
        FOREIGN KEY (related_product_id) REFERENCES product (product_id)
        ON DELETE CASCADE
);

9. Typische Fehler und Anti-Patterns

Der häufigste Fehler bei einer Many-to-Many-Beziehung ist die kommaseparierte Liste von IDs in einer Textspalte, meist aus vermeintlicher Vereinfachung entstanden. Sie verhindert Indizierung, referenzielle Integrität und macht selbst einfache Abfragen zu fehleranfälligen String-Operationen. Ein zweiter verbreiteter Fehler ist das Fehlen des zweiten Index auf der Junction-Tabelle, wodurch Abfragen in eine Richtung schnell, in die andere Richtung aber katastrophal langsam werden, oft unbemerkt, bis die Tabelle eine kritische Größe erreicht.

Ein dritter Fehler ist das Nachladen veränderlicher Werte statt sie zum Zeitpunkt der Beziehung zu kopieren, etwa wenn eine Bestellung den aktuellen statt den historischen Produktpreis anzeigt. Ein vierter, subtilerer Fehler ist das Fehlen eines UNIQUE-Constraints bei Verwendung eines Surrogate Keys: Ohne diesen Constraint kann dieselbe Kombination aus zwei Fremdschlüsseln versehentlich mehrfach eingefügt werden, was Aggregationen wie COUNT verfälscht und zu doppelt gezählten Beziehungen führt.

10. Zusammenfassung

Eine Many-to-Many-Beziehung lässt sich im relationalen Modell nur über eine Junction-Tabelle korrekt abbilden, die die Beziehung in zwei One-to-Many-Beziehungen auflöst. Ob ein Composite Primary Key oder ein Surrogate Key mit begleitendem UNIQUE-Constraint zum Einsatz kommt, hängt davon ab, ob die Junction-Tabelle reine Verknüpfungsinfrastruktur bleibt oder selbst zur fachlichen Entität mit eigenen Attributen wird. Fremdschlüssel mit expliziten ON DELETE-Regeln sichern die referenzielle Integrität, ein zweiter Index auf die jeweils andere Fremdschlüsselspalte sichert die Performance in beide Abfragerichtungen.

Wer diese Grundregeln beachtet, vermeidet die häufigsten Fehler: kommaseparierte ID-Listen, fehlende Indizes in der Rückrichtung und veränderliche Werte, die zum falschen Zeitpunkt nachgeladen statt zum richtigen Zeitpunkt kopiert werden. Eine sauber modellierte Many-to-Many-Beziehung bleibt auch bei wachsender Datenmenge performant und lässt sich ohne strukturelle Umbauten um neue Attribute erweitern.

11. FAQ: Many-to-Many-Beziehungen richtig modellieren