Урок 05

Проектирование связей

Три типа связей

1:1 — один к одному.
1:N — один ко многим.
N:N — многие ко многим.

Правило: в реляционной БД любая связь реализуется через внешний ключ (FK).

Один к одному (1:1)

Когда:

Разделение на «основное + детали» — редкие поля отдельно.
Разделение по правам — секретные данные в отдельной таблице.
Оптимизация — тяжёлые колонки не читать постоянно.
Логические группы — users + user_settings.

Реализация:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name TEXT NOT NULL
);

CREATE TABLE user_profiles (
    user_id BIGINT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    bio TEXT,
    avatar_url TEXT,
    birth_date DATE
);

Ключевое:

user_id — и PK, и FK. Это гарантирует 1:1.
ON DELETE CASCADE — профиль не нужен без пользователя.

⚠️ Если PK профиля — отдельный id, то возможны дубли. Не делайте так.

Альтернатива — общая таблица:

Если поля всегда читаются вместе — 1:1 не нужен. Держите в одной таблице.

Правило: 1:1 оправдано редко. Часто это преждевременная оптимизация.

Один ко многим (1:N)

Основной тип связи.

Пример: клиент → заказы.

CREATE TABLE customers (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_orders_customer ON orders (customer_id);

Что важно:

FK в дочерней таблице — orders.customer_id.
Индекс на FK — для быстрого JOIN и CASCADE.
NOT NULL — если заказ всегда у клиента.

ON DELETE — выбор:

CASCADE — удалить заказы при удалении клиента.
RESTRICT — запретить удаление.
SET NULL — заказы станут без клиента (нужно NOT NULL убрать).

Правило:

Комментарии, лайки, товары в заказе — CASCADE.
Справочники (категории, бренды) — RESTRICT.
Опциональная связь — SET NULL.

Многие ко многим (N:N)

Реализация: через связующую (junction) таблицу.

Пример: посты ↔ теги.

CREATE TABLE posts (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE tags (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE post_tags (
    post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id BIGINT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

CREATE INDEX idx_post_tags_tag ON post_tags (tag_id);

Ключевое:

Составной PK — (post_id, tag_id) — гарантирует уникальность.
Индекс на второй ключ — tag_id — для обратного поиска.
ON DELETE CASCADE с обеих сторон.

Что даёт:

Нет дублей связей.
Можно добавить атрибуты связи (см. ниже).

Атрибуты связи

Связь может иметь собственные атрибуты:

Пример: заказ ↔ товар.

CREATE TABLE order_items (
    order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price_at_purchase NUMERIC(10, 2) NOT NULL,
    discount NUMERIC(10, 2) NOT NULL DEFAULT 0,
    PRIMARY KEY (order_id, product_id)
);

Что здесь:

quantity — сколько.
price_at_purchase — snapshot цены.
discount — скидка на позицию.

Правило: связь — это тоже сущность. Если у неё есть атрибуты — они в связующей таблице.

Самореферентные связи

Пример: категории с родителями.

CREATE TABLE categories (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    parent_id BIGINT REFERENCES categories(id) ON DELETE SET NULL
);

CREATE INDEX idx_categories_parent ON categories (parent_id);

Что даёт: дерево категорий.

Запросы:

Все дети:

SELECT * FROM categories WHERE parent_id = 5;

Дерево (рекурсивный CTE):

WITH RECURSIVE tree AS (
    SELECT id, name, parent_id, 1 AS depth, ARRAY[id] AS path
    FROM categories WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id, t.depth + 1, t.path || c.id
    FROM categories c
    JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY path;

Что даёт: полная иерархия одним запросом.

⚠️ Глубокие деревья (10+ уровней) — медленно. Для них — ltree (PostgreSQL) или materialized path.

Полиморфные связи

Проблема: комментарии могут быть у постов, товаров, статей.

Вариант 1 — nullable FK:

CREATE TABLE comments (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    post_id BIGINT REFERENCES posts(id),
    product_id BIGINT REFERENCES products(id),
    article_id BIGINT REFERENCES articles(id),
    body TEXT NOT NULL,
    CHECK (num_nonnulls(post_id, product_id, article_id) = 1)
);

Плюс: FK работают. Минус: много nullable колонок.

Вариант 2 — polymorphic (Rails-style):

CREATE TABLE comments (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    commentable_type TEXT NOT NULL,
    commentable_id BIGINT NOT NULL,
    body TEXT NOT NULL
);

CREATE INDEX idx_comments_poly ON comments (commentable_type, commentable_id);

Плюс: гибко. Минус: нет FK, возможны сироты.

Вариант 3 — отдельные таблицы:

post_comments
product_comments
article_comments

Плюс: целостность, свои поля. Минус: дублирование структуры.

Правило:

Мало типов — вариант 1.
Много, часто меняются — вариант 2 (без FK, но с CHECK).
Разные поля — вариант 3.

Многие ко многим с атрибутами vs сущность

Ситуация: нужна «таблица подписок» — user ↔ channel, с датой и статусом.

Это уже сущность, а не связь.

CREATE TABLE subscriptions (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    channel_id BIGINT NOT NULL REFERENCES channels(id),
    status TEXT NOT NULL DEFAULT 'active',
    subscribed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    unsubscribed_at TIMESTAMPTZ,

    UNIQUE (user_id, channel_id)
);

Что даёт:

id — проще ссылаться.
Больше атрибутов.
История через status.

Правило: если у связи > 3 атрибутов или сложная логика — делайте отдельную сущность с id.

Реализация FK

На уровне схемы (правильно):

CREATE TABLE orders (
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE
);

На уровне приложения (плохо):

// Проверка в коде
if (!User::find($user_id)) throw new Exception();
$order = Order::create([...]);
// Гонка: user удалён между проверкой и insert

Правило: всегда FK в схеме. Проверка на уровне приложения — дополнительно, не вместо.

Отключение FK (для bulk load):

SET FOREIGN_KEY_CHECKS = 0;
-- массовый INSERT
SET FOREIGN_KEY_CHECKS = 1;

⚠️ Только для миграций и seeding. Проверяйте данные перед включением обратно.

Deferred constraints

PostgreSQL:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id)
DEFERRABLE INITIALLY DEFERRED;

Что даёт: проверка FK откладывается до конца транзакции. Можно вставить заказ до пользователя в той же транзакции.

Когда: циклические ссылки, сложные миграции.

Проблемы и решения

1. Циклические FK:

users.company_id → companies
companies.owner_id → users

Решение: DEFERRABLE или разрешить NULL.

2. Удаление с CASCADE по цепочке:

Удалили user → cascade удалил orders → cascade удалил order_items → cascade удалил ...

⚠️ Может занять время. И нельзя остановить.

Решение: soft delete + периодический hard delete.

3. FK на большую таблицу тормозит:

INSERT в orders проверяет users

Решение: индекс на FK обязателен.

4. Осиротевшие записи:

FK отключён → данные невалидны

Решение: проверка LEFT JOIN ... WHERE parent.id IS NULL.

5. Нет уникальности в N:N:

post_tags без PK → дубли

Решение: составной PK.

Практика: схемы связей

1. 1:1 — users + user_settings:

CREATE TABLE user_settings (
    user_id BIGINT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    theme TEXT NOT NULL DEFAULT 'light',
    language TEXT NOT NULL DEFAULT 'ru',
    notifications_enabled BOOLEAN NOT NULL DEFAULT TRUE
);

2. 1:N — customers + orders:

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    ...
);
CREATE INDEX idx_orders_customer ON orders (customer_id);

3. N:N — posts + tags:

CREATE TABLE post_tags (
    post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id BIGINT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);
CREATE INDEX idx_post_tags_tag ON post_tags (tag_id);

4. Самореферентная — categories:

CREATE TABLE categories (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    parent_id BIGINT REFERENCES categories(id) ON DELETE SET NULL
);
CREATE INDEX idx_categories_parent ON categories (parent_id);

5. Полиморфная — comments:

CREATE TABLE comments (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    commentable_type TEXT NOT NULL,
    commentable_id BIGINT NOT NULL,
    body TEXT NOT NULL,
    CHECK (commentable_type IN ('post', 'product', 'article'))
);
CREATE INDEX idx_comments_poly ON comments (commentable_type, commentable_id);

Итог

1:1 — user_id как PK + FK.
1:N — FK в дочерней + индекс.
N:N — связующая таблица + составной PK.
Атрибуты связи — в связующей таблице.
Самореферентная — parent_id + рекурсивный CTE.
Полиморфная — 3 варианта, у каждого минусы.
FK всегда в схеме.
Индекс на FK обязателен.
DEFERRABLE — для циклических.

Дальше — индексы: глубокое погружение.