Zurück zu allen Anleitungen
Datenbankentwurf··6 Min. Lesezeit

Eins-zu-viele oder viele-zu-viele: SQL-Beziehungsmuster erklärt

Lerne Eins-zu-viele- im Vergleich zu Viele-zu-viele-Beziehungen in SQL. Siehe, wie man jede davon mit Fremdschlüsseln, Verbindungstabellen und Beispielen aus der Praxis implementiert.

Fremdschlüssel von Customer zu Order und Verbindungstabelle von Student zu Course mit einem zusammengesetzten Primärschlüssel
In diesem Leitfaden
  1. SQL-Beziehungsmuster verstehen
  2. Eins-zu-viele-Beziehungen
  3. Viele-zu-Viele-Beziehungen
  4. Eins-zu-Eins-Beziehungen
  5. Wie Beziehungen in ERD-Builder dargestellt werden
  6. Auswahl des richtigen Beziehungsmusters
  7. Referenzen und weiterführende Literatur

SQL-Beispiele verwenden PostgreSQL, sofern kein anderes Dialekt genannt wird. Kurze Schnipsel setzen voraus, dass ihre referenzierten Tabellen bereits existieren. Validieren Sie DDL in Ihrer Zieldatenbank, bevor Sie sie anwenden.

SQL-Beziehungsmuster verstehen

Relationale Datenbanken beziehen ihre Stärke aus den Verbindungen zwischen Tabellen. Egal, ob Sie eine kleine Anwendung oder ein Enterprise-Data-Warehouse entwerfen, die Wahl des richtigen Beziehungsmusters bestimmt, wie effizient Sie Ihre Daten speichern, abfragen und verwalten können. Die drei grundlegenden Muster—eins zu eins, eins zu viele, und Viele-zu-Viele— decken viele gängige Assoziationen ab; rekursive Beziehungen und Beziehungen höheren Grades erfordern zusätzliche Modellierung.

In dieser Anleitung zerlegen wir jedes Muster mit PostgreSQL-Beispielen, untersuchen die Rolle von Verknüpfungstabellen und zeigen, wie diese Beziehungen in einem Entity-Relationship-Diagramm (ERD) dargestellt werden. Wenn Sie Ihr eigenes Schema sofort visualisieren möchten, öffnen Sie die Erd Go Editor und fügen Sie unterstützte CREATE TABLE Definitionen zum Generieren eines Diagramms.

Fremdschlüssel-SQL, abgebildet auf eine ERD-Beziehung zwischen Kunden und Bestellungen
Ein Fremdschlüssel ist mehr als nur Syntax: In einem ERD wird er zur Beziehungslinie, die erklärt, wie Tabellen voneinander abhängen.

Eins-zu-viele-Beziehungen

A eins zu viele (1:N) Beziehung ist das häufigste Muster im Datenbankdesign. Es bedeutet, dass eine einzelne Zeile in der Elternteil Tabelle kann in diesem Fall von vielen Zeilen in der untergeordnet Tabelle, und ein nicht-null Fremdschlüssel eines Kindes verweist auf ein Elternelement. Ein nullable Fremdschlüssel erlaubt ein Kind ohne Elternelement.

Klassisches Beispiel: Abteilungen und Mitarbeiter

Ein Unternehmen hat viele Abteilungen, und jede Abteilung enthält mehrere Mitarbeiter. Jeder Mitarbeiter gehört jedoch zu genau einer Abteilung zu einem bestimmten Zeitpunkt.

CREATE TABLE departments (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    location    VARCHAR(255),
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE employees (
    id              SERIAL PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    email           VARCHAR(255) UNIQUE NOT NULL,
    department_id   INT NOT NULL,
    hire_date       DATE DEFAULT CURRENT_DATE,
    CONSTRAINT fk_department
        FOREIGN KEY (department_id)
        REFERENCES departments (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Wichtige Implementierungsdetails

  • Der Fremdschlüssel (department_id) befindet sich immer auf dem viele Seite – die employees Tabelle referenziert werden.
  • ON DELETE RESTRICT verhindert das versehentliche Entfernen einer Abteilung, der noch Mitarbeiter zugewiesen sind.
  • ON UPDATE CASCADE stellt sicher, dass sich bei einer Änderung des Primärschlüssels einer Abteilung alle Mitarbeiterzeilen automatisch aktualisieren.
  • Hinzufügen eines Index zu department_id kann Joins und Abteilungsfilter unterstützen; bestätigen Sie den Nutzen mit repräsentativen Daten und einem Ausführungsplan.

Abfragen von Eins-zu-viele-Daten

-- All employees in the Engineering department
SELECT e.first_name, e.last_name, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE d.name = 'Engineering';

-- Count employees per department
SELECT d.name, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY headcount DESC;

Weitere alltägliche Eins-zu-viele-Beispiele umfassen Autoren → Blogbeiträge, Kunden → Bestellungen, und Kategorien → Produkte.

Viele-zu-Viele-Beziehungen

A Viele-zu-Viele (M:N) Beziehung existiert, wenn Zeilen auf beide Seiten können mit mehreren Zeilen auf der anderen Seite verknüpft werden. Relationale Datenbanken können dies nicht direkt mit einem einzelnen Fremdschlüssel darstellen, daher führen wir ein Verknüpfungstabelle (auch als Brücke, Verknüpfung, oder assoziativ Tabelle).

Klassisches Beispiel: Studenten und Kurse

Ein Student kann sich für viele Kurse einschreiben, und jeder Kurs kann viele Studenten haben. Die Verknüpfungstabelle enrollments befindet sich dazwischen und enthält die Fremdschlüssel für beide Seiten.

CREATE TABLE students (
    id          SERIAL PRIMARY KEY,
    full_name   VARCHAR(120) NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL,
    enrolled_on DATE DEFAULT CURRENT_DATE
);

CREATE TABLE courses (
    id          SERIAL PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    credits     SMALLINT NOT NULL DEFAULT 3,
    department  VARCHAR(100)
);

CREATE TABLE enrollments (
    student_id  INT NOT NULL REFERENCES students (id) ON DELETE CASCADE,
    course_id   INT NOT NULL REFERENCES courses (id) ON DELETE CASCADE,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    grade       CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

Warum eine Zwischentabelle?

  • Das zusammengesetzter Primärschlüssel (student_id, course_id) erlaubt eine Zeile pro Studenten-Kurs-Paar. Wenn wiederholte Einschreibungen über mehrere Semester hinweg erforderlich sind, fügen Sie ein Semester in den Business Key ein oder modellieren Sie jede Einschreibung separat.
  • Sie können der Beziehung selbst zusätzliche Attribute anhängen, wie zum Beispiel grade oder enrolled_at, die zu keiner der beiden Entitäten allein gehören.
  • ON DELETE CASCADE bei beiden Fremdschlüsseln bedeutet, dass das Entfernen eines Studenten oder Kurses automatisch die Einschreibungszeilen bereinigt.

Abfragen von Viele-zu-Viele-Daten

-- All courses for a specific student
SELECT c.title, c.credits, e.grade
FROM enrollments e
JOIN courses c ON c.id = e.course_id
WHERE e.student_id = 42;

-- Students enrolled in 'Database Systems'
SELECT s.full_name, s.email
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
WHERE c.title = 'Database Systems';

Beispiele aus der Praxis für Many-to-Many umfassen Benutzer ↔ Rollen, Produkte ↔ Tags, actors ↔ movies, und Bestellungen ↔ Produkte (über ein order_items Verknüpfungstabelle, die zusätzlich Menge und Preis speichert).

Eins-zu-Eins-Beziehungen

A eins zu eins (1:1) Beziehung bedeutet, dass jede Zeile in Tabelle A mit höchstens einer Zeile in Tabelle B übereinstimmt und umgekehrt. Dieses Muster ist weniger verbreitet, aber nützlich zum Aufteilen von selten abgerufenen oder sensiblen Spalten in eine separate Tabelle.

CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    username    VARCHAR(60) UNIQUE NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE user_profiles (
    user_id     INT PRIMARY KEY REFERENCES users (id) ON DELETE CASCADE,
    bio         TEXT,
    avatar_url  VARCHAR(500),
    birthdate   DATE,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Beachten Sie, dass user_profiles.user_id ist sowohl das als auch Primärschlüssel und das Fremdschlüssel. Diese Beschränkung erzwingt die Eins-zu-Eins-Zuordnung auf Datenbankebene – jeder Benutzer kann maximal eine Profilzeile haben.

Wie Beziehungen in ERD-Builder dargestellt werden

Ein Entity-Relationship-Diagramm verwendet Linien und Symbole zwischen Tabellen, um die Kardinalität auf einen Blick zu vermitteln. Die beiden beliebtesten Notationen sind Krähenfuß und Chen Notation.

BeziehungKrähenfuß-SymbolWie man liest
Eins-zu-Eins||—||Genau eine auf jeder Seite
Eins zu vielen||—<Eins zu vielen (Krähenfußende)
Viele-zu-Viele>—<Viele auf beiden Seiten; implementiert über eine Verknüpfungstabelle

Diese Textsymbole sind Kurzformen für die maximale Kardinalität. Optionalität erfordert eine zusätzliche Null- oder Eins-Markierung; ein Fremdschlüssel allein erfordert nicht, dass ein übergeordnetes Element Kinder hat.

Wenn Sie eine n:m-Beziehung in einem ERD-Builder modellieren, sehen Sie typischerweise drei Kästchen – zwei Entitätstabellen und die dazwischen liegende Zwischentabelle, die jeweils durch eine 1:n-Linie verbunden sind.

Fügen Sie eines der SQL-Beispiele aus diesem Artikel ein in Erd Go Editor um das automatisch generierte Diagramm zu sehen, komplett mit Fremdschlüssel-Pfeilen und Kardinalitätsindikatoren.

Auswahl des richtigen Beziehungsmusters

Die Auswahl der falschen Kardinalität führt zu Datenduplizierung, komplexen Abfragen und Integritätsfehlern. Verwenden Sie dieses Entscheidungsframework:

  • Kann eine Zeile auf Seite A sich auf viele Zeilen auf Seite B beziehen, aber nicht umgekehrt? → Eins-zu-Viele. Platzieren Sie den Fremdschlüssel auf Seite B.
  • Können sich Zeilen auf beiden Seiten auf viele Zeilen der jeweils anderen Seite beziehen? → Viele-zu-Viele. Erstellen Sie eine Zwischentabelle mit zusammengesetztem Schlüssel.
  • Ist die Zuordnung streng 1:1 und Sie benötigen eine Tabellentrennung? → Eins-zu-Eins. Verwenden Sie denselben Primärschlüssel als Fremdschlüssel.

Skizzieren Sie im Zweifelsfall zuerst Ihr Schema. Ein schnelles ERD deckt Beziehungsprobleme viel früher auf, als wenn Sie in der Produktion auf Constraint-Fehler stoßen. Versuchen Sie, Ihr Diagramm im zu erstellen Erd Go Editor— es dauert Sekunden und kann Stunden an Debugging sparen.

Referenzen und weiterführende Literatur

Weitere Beiträge