Powrót do wszystkich przewodników
Projektowanie baz danych··6 min czytania

Jeden do wielu czy wiele do wielu: wzorce relacji SQL

Dowiedz się o relacjach jeden-do-wielu vs wiele-do-wielu w SQL. Zobacz, jak zaimplementować każdą z nich za pomocą kluczy obcych, tabel łączących i rzeczywistych przykładów.

Klucz obcy klient-zamówienie i tabela łącząca student-kurs ze złożonym kluczem głównym
W tym przewodniku
  1. Zrozumienie wzorców relacji SQL
  2. Relacje jeden do wielu
  3. Relacje wiele do wielu
  4. Relacje jeden do jednego
  5. Jak relacje pojawiają się w ERD
  6. Wybór właściwego wzorca relacji
  7. Referencje i dalsza lektura

Przykłady SQL używają PostgreSQL, chyba że wymieniono inny dialekt. Krótkie fragmenty zakładają, że tabele, do których się odwołują, już istnieją. Zweryfikuj DDL w docelowej bazie danych przed jego zastosowaniem.

Zrozumienie wzorców relacji SQL

Relacyjne bazy danych czerpią swoją moc z połączeń między tabelami. Niezależnie od tego, czy projektujesz małą aplikację, czy korporacyjną hurtownię danych, wybór właściwego wzorca relacji określa, jak efektywnie możesz przechowywać, odpytywać i utrzymywać swoje dane. Trzy podstawowe wzorce—jeden do jednego, jeden do wielu, oraz wiele do wielu—obejmują wiele powszechnych powiązań; relacje rekurencyjne i wyższego stopnia wymagają dodatkowego modelowania.

W tym przewodniku omawiamy każdy wzorzec na przykładach z PostgreSQL, badamy rolę tabel łączących i pokazujemy, jak te relacje wyglądają na diagramie encji i relacji (ERD). Jeśli chcesz teraz zwizualizować własny schemat, otwórz Edytor Erd Go i wklejanie obsługiwane CREATE TABLE definicje do wygenerowania diagramu.

SQL klucza obcego zmapowany na relację ERD między klientami a zamówieniami
Klucz obcy to coś więcej niż składnia: w ERD staje się linią relacji, która wyjaśnia, jak tabele zależą od siebie nawzajem.

Relacje jeden do wielu

A jeden do wielu Relacja (1:N) jest najczęstszym wzorcem w projektowaniu baz danych. Oznacza to, że pojedynczy wiersz w rodzic tabela może być przywoływana przez wiele wierszy w podrzędny tabela, a niepusty klucz obcy potomny wskazuje na jednego rodzica. Klucz obcy dopuszczający wartość null pozwala na element potomny bez rodzica.

Klasyczny przykład: Działy i Pracownicy

Firma posiada wiele działów, a każdy dział zawiera wielu pracowników. Jednak każdy pracownik należy do dokładnie jednego działu w danym czasie.

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
);

Kluczowe szczegóły implementacji

  • Klucz obcy (department_id) zawsze znajduje się na wiele stronę — employees tabela w tym przypadku.
  • ON DELETE RESTRICT zapobiega przypadkowemu usunięciu działu, do którego wciąż są przypisani pracownicy.
  • ON UPDATE CASCADE zapewnia, że jeśli klucz podstawowy działu ulegnie zmianie, wszystkie wiersze pracowników zostaną zaktualizowane automatycznie.
  • Dodawanie indeksu na department_id może pomóc w złączeniach i filtrach działów; potwierdź korzyść za pomocą reprezentatywnych danych i planu wykonania.

Zapytania o Dane jeden-do-wielu

-- 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;

Inne codzienne przykłady relacji jeden-do-wielu obejmują autorzy → posty na blogu, customers → orders, oraz kategorie → produkty.

Relacje wiele do wielu

A wiele do wielu Relacja (M:N) istnieje, gdy wiersze na oba strony mogą być powiązane z wieloma wierszami po drugiej stronie. Relacyjne bazy danych nie mogą reprezentować tego bezpośrednio za pomocą pojedynczego klucza obcego, więc wprowadzamy tabela łącząca (nazywany również most, złączenie, lub asocjacyjne tabela).

Klasyczny przykład: Studenci i Kursy

Student może zapisać się na wiele kursów, a każdy kurs może mieć wielu studentów. Tabela łącząca enrollments znajduje się między nimi i przechowuje klucze obce do obu stron.

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)
);

Dlaczego tabela łącząca?

  • Ten złożony klucz główny (student_id, course_id) pozwala na jeden wiersz na parę student–kurs. Jeśli wymagane jest powtarzanie zapisów w różnych semestrach, uwzględnij semestr w kluczu biznesowym lub wymodeluj każdy zapis oddzielnie.
  • Możesz dołączyć dodatkowe atrybuty do samej relacji, takie jak grade lub enrolled_at, które nie należą wyłącznie do żadnej z encji.
  • ON DELETE CASCADE na obu kluczach obcych oznacza, że usunięcie studenta lub kursu automatycznie czyści wiersze zapisów.

Zapytania o Dane wiele-do-wielu

-- 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';

Przykłady relacji wiele-do-wielu ze świata rzeczywistego obejmują użytkownicy ↔ role, produkty ↔ tagi, aktorzy ↔ filmy, oraz zamówienia ↔ produkty (poprzez order_items tabela łącząca, która przechowuje również ilość i cenę).

Relacje jeden do jednego

A jeden do jednego Relacja (1:1) oznacza, że każdy wiersz w Tabeli A mapuje się na co najwyżej jeden wiersz w Tabeli B i odwrotnie. Ten wzorzec jest rzadszy, ale przydatny do dzielenia rzadko używanych lub wrażliwych kolumn na oddzielną tabelę.

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
);

Zauważ, że user_profiles.user_id jest zarówno klucz główny i klucz obcy. To ograniczenie wymusza mapowanie jeden-do-jednego na poziomie bazy danych — każdy użytkownik może mieć co najwyżej jeden wiersz profilu.

Jak relacje pojawiają się w ERD

Diagram związków encji wykorzystuje linie i symbole między tabelami, aby na pierwszy rzut oka przekazać liczność. Dwie najpopularniejsze notacje to Kurza łapka i Chen notacja.

RelacjaSymbol kurzej łapkiJak czytać
Jeden do jednego||—||Dokładnie jeden po każdej stronie
Jeden do wielu||—<Jeden po lewej, wiele po prawej (zakończenie typu kurza stopka)
Wiele do wielu>—<Wiele po obu stronach; zaimplementowane poprzez tabelę łączącą

Te symbole tekstowe są skrótem dla maksymalnej krotności. Opcjonalność wymaga dodatkowego znacznika zero lub jeden; sam klucz obcy nie wymaga, aby rodzic posiadał jakiekolwiek dzieci.

Podczas modelowania relacji wiele-do-wielu w narzędziu ERD, zazwyczaj widać trzy pola — dwie tabele encji i tabelę łączącą między nimi, z których każda jest połączona linią jeden-do-wielu.

Wklej dowolny z przykładów SQL z tego artykułu do Edytor Erd Go aby zobaczyć diagram wygenerowany automatycznie, wraz ze strzałkami kluczy obcych i wskaźnikami liczności.

Wybór właściwego wzorca relacji

Wybór niewłaściwej liczności prowadzi do duplikacji danych, złożonych zapytań i błędów integralności. Skorzystaj z tego schematu decyzyjnego:

  • Czy jeden wiersz po stronie A może odnosić się do wielu wierszy po stronie B, ale nie odwrotnie? → Jeden do wielu. Umieść klucz obcy po stronie B.
  • Czy wiersze po obu stronach mogą odnosić się do wielu wierszy po drugiej stronie? → Wiele do wielu. Utwórz tabelę łączącą z kluczem złożonym.
  • Czy mapowanie jest ściśle 1:1 i potrzebujesz separacji tabel? → Jeden do jednego. Użyj tego samego klucza podstawowego jako klucza obcego.

W razie wątpliwości najpierw naszkicuj swój schemat. Szybki ERD ujawnia problemy z relacjami znacznie wcześniej niż napotkanie błędów ograniczeń na produkcji. Spróbuj zbudować swój diagram w Edytor Erd Go—zajmuje to sekundy i może zaoszczędzić godziny debugowania.

Referencje i dalsza lektura

Więcej postów