Какие знаешь связи в реляционной БД?

Связи (Relationships) в реляционной БД

Основные типы связей

1. One-to-One (1:1)

Определение: Одна запись в таблице A связана с ровно одной записью в таблице B

Характеристика: обе стороны имеют уникальность

Реализация:

-- Таблица 1
CREATE TABLE User (
  id UUID PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255)
);

-- Таблица 2
CREATE TABLE UserProfile (
  id UUID PRIMARY KEY,
  user_id UUID UNIQUE NOT NULL,  -- UNIQUE делает связь 1:1
  bio TEXT,
  avatar_url VARCHAR(500),
  FOREIGN KEY (user_id) REFERENCES User(id)
);

Диаграмма:

User (1) ──── (1) UserProfile

Когда использовать:

• Дополнительная информация отделена в отдельную таблицу
• Профиль пользователя
• Документ и его метаданные
• Паспортные данные сотрудника

Пример из PrepBro:

User (1) ──── (1) UserStatistics
  (user может иметь ровно одну строку статистики)

2. One-to-Many (1:N)

Определение: Одна запись в таблице A связана с несколькими записями в таблице B

Характеристика: A - родитель, B - дети

Реализация:

-- Таблица 1 (Родитель)
CREATE TABLE Question (
  id UUID PRIMARY KEY,
  title VARCHAR(500) NOT NULL,
  profession_id UUID NOT NULL,
  FOREIGN KEY (profession_id) REFERENCES Profession(id)
);

-- Таблица 2 (Дети)
CREATE TABLE Answer (
  id UUID PRIMARY KEY,
  question_id UUID NOT NULL,  -- FK без UNIQUE = 1:N
  text TEXT NOT NULL,
  user_id UUID NOT NULL,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (question_id) REFERENCES Question(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES User(id)
);

Диаграмма:

Question (1) ────< (N) Answer
      ║
      ║ каждый вопрос имеет много ответов
      ║
      └─ Answer #1
      └─ Answer #2
      └─ Answer #3

Когда использовать:

• Самая частая связь
• Один ко многим
• Родитель-ребёнок

Примеры из PrepBro:

Question (1) ──────< (N) Answer
User (1) ────────< (N) Answer
Answer (1) ──────< (N) Comment
Profession (1) ──< (N) Question

Каскадное удаление:

-- При удалении Question удаляются все Answer
FOREIGN KEY (question_id) REFERENCES Question(id)
ON DELETE CASCADE;

-- При удалении User ответы остаются, но user_id = NULL
FOREIGN KEY (user_id) REFERENCES User(id)
ON DELETE SET NULL;

3. Many-to-Many (N:M)

Определение: Много записей в A связано со многими записями в B

Характеристика: оба конца множественные

Реализация (через junction table):

-- Таблица 1
CREATE TABLE User (
  id UUID PRIMARY KEY,
  name VARCHAR(255)
);

-- Таблица 2
CREATE TABLE Skill (
  id UUID PRIMARY KEY,
  name VARCHAR(255)  -- e.g., "SQL", "REST API", "Caching"
);

-- Таблица связи (Junction/Bridge/Linking Table)
CREATE TABLE UserSkill (
  user_id UUID NOT NULL,
  skill_id UUID NOT NULL,
  level VARCHAR(50),  -- Дополнительная информация: beginner, intermediate, expert
  years_of_experience INT,
  
  PRIMARY KEY (user_id, skill_id),  -- Composite primary key
  FOREIGN KEY (user_id) REFERENCES User(id) ON DELETE CASCADE,
  FOREIGN KEY (skill_id) REFERENCES Skill(id) ON DELETE CASCADE
);

Диаграмма:

        ┌─── Skill #1 (SQL)
        │
User #1 ├─── Skill #2 (Python)
        │
        └─── Skill #3 (Docker)

        ┌─── User #1
        │
Skill #2├─── User #2
(Python)│
        └─── User #5

Когда использовать:

• Студент и курсы (студент может учить много курсов)
• Пользователь и роли (пользователь может иметь много ролей)
• Теги и посты (пост может иметь много тегов)
• Производитель и продукты

Примеры из PrepBro:

User (N) ──< UserSkill >─ (M) Skill
         ║
         └─ интеграционная таблица с доп информацией
             (level, years, proficiency)

Question (N) ──< QuestionTag >─ (M) Tag
            ║
            └─ позволяет одному вопросу иметь много тегов

4. Recursive (Самоссылающаяся связь)

Определение: Таблица ссылается сама на себя

Реализация:

-- Иерархия категорий
CREATE TABLE Category (
  id UUID PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  parent_category_id UUID,  -- Самоссылка
  FOREIGN KEY (parent_category_id) REFERENCES Category(id)
ON DELETE SET NULL
);

-- Пример данных:
-- System Design (parent_category_id = NULL)
--   ├─ Scalability (parent_category_id = System Design)
--   ├─ Database Design (parent_category_id = System Design)
--   └─ API Design (parent_category_id = System Design)

Диаграмма:

Category
├─ System Design (root)
│  ├─ Scalability (child)
│  ├─ Database Design (child)
│  └─ API Design (child)
└─ Backend (root)
   ├─ Python (child)
   ├─ Node.js (child)

SQL для дерева:

-- Получить все вопросы в категории и подкатегориях
WITH RECURSIVE category_tree AS (
  SELECT id FROM Category WHERE id = 'system-design'
  UNION ALL
  SELECT c.id FROM Category c
  INNER JOIN category_tree ct ON c.parent_category_id = ct.id
)
SELECT * FROM Question
WHERE category_id IN (SELECT id FROM category_tree);

5. Polymorphic (Полиморфная связь)

Определение: Один FK может указывать на разные таблицы

Реализация:

-- Комментарии могут быть на Answer или Comment
CREATE TABLE Comment (
  id UUID PRIMARY KEY,
  author_id UUID NOT NULL,
  text TEXT NOT NULL,
  
  -- Полиморфная ссылка
  commentable_type VARCHAR(50),  -- 'Answer' или 'Comment'
  commentable_id UUID,  -- ID Answer или ID Comment
  
  FOREIGN KEY (author_id) REFERENCES User(id)
);

-- Пример:
-- Comment #1: commentable_type='Answer', commentable_id='answer-123'
-- Comment #2: commentable_type='Comment', commentable_id='comment-1'

Когда использовать:

• Когда один объект может быть принадлежит разным типам
• Комментарии к разным типам контента
• Лайки разных объектов
• Избегай если можно! Лучше normale N:M связи

Сравнение связей

Ограничения целостности при связях

ON DELETE

CASCADE - удалить всех детей

FOREIGN KEY (question_id) REFERENCES Question(id)
ON DELETE CASCADE;
-- Когда удалим Question → удалятся все Answer

SET NULL - установить NULL

FOREIGN KEY (author_id) REFERENCES User(id)
ON DELETE SET NULL;
-- Когда удалим User → author_id станет NULL

RESTRICT - запретить удаление

FOREIGN KEY (profession_id) REFERENCES Profession(id)
ON DELETE RESTRICT;
-- Не сможешь удалить Profession если на неё ссылаются Question'ы

SET DEFAULT - установить значение по умолчанию

FOREIGN KEY (moderator_id) REFERENCES User(id)
ON DELETE SET DEFAULT DEFAULT NULL;

ON UPDATE

Аналогичные опции для обновления PK

ON DELETE CASCADE
ON UPDATE RESTRICT;  -- Запретить изменение user_id если есть ссылки

Индексы для связей

Всегда добавляй индексы на FK для скорости JOIN'ов:

-- Автоматически создан на PK
CREATE INDEX idx_answer_question_id ON Answer(question_id);
CREATE INDEX idx_answer_user_id ON Answer(user_id);
CREATE INDEX idx_comment_answer_id ON Comment(answer_id);

Диаграмма PrepBro

┌─────────────────┐
│   Profession    │
│   (PK: id)      │
├─────────────────┤
│ id              │
│ name            │
└─────────────────┘
         ↑
         │ (1:N)
         │
┌─────────────────┐       ┌──────────────────┐
│   Question      │       │      User        │
│   (PK: id)      │       │    (PK: id)      │
├─────────────────┤       ├──────────────────┤
│ id              │       │ id               │
│ title           │       │ email            │
│ profession_id   │─FK──→ │ name             │
└─────────────────┘       └──────────────────┘
         ↑                        ↑
         │ (1:N)                 │ (1:N)
         │                       │
         └──┬──────────────────┬─┘
            │                  │
      ┌─────────────────┐
      │     Answer      │
      │    (PK: id)     │
      ├─────────────────┤
      │ id              │
      │ question_id ────┼─FK→ Question
      │ user_id ────────┼─FK→ User
      │ text            │
      └─────────────────┘
            ↑
            │ (1:N)
            │
      ┌─────────────────┐
      │    Comment      │
      │    (PK: id)     │
      ├─────────────────┤
      │ id              │
      │ answer_id ──────┼─FK→ Answer
      │ user_id ────────┼─FK→ User
      │ text            │
      └─────────────────┘

N:M Пример (Теги):
      ┌──────────────┐
      │     Tag      │
      │   (PK: id)   │
      ├──────────────┤
      │ id           │
      │ name         │
      └──────────────┘
             ↑
             │ (N:M)
             │
      ┌──────────────────┐
      │  QuestionTag     │
      │  (PK: q_id+t_id) │
      ├──────────────────┤
      │ question_id ─────┼─FK→ Question
      │ tag_id ──────────┼─FK→ Tag
      └──────────────────┘

Правила для System Analyst

✓ Используй 1:N для иерархий

• Вопрос → Ответы
• Категория → Подкатегории

✓ Используй N:M для классификаций

• Вопрос → Теги
• Пользователь → Профессии

✓ Всегда определи каскадное удаление

• Что происходит при удалении родителя?
• CASCADE, SET NULL, или RESTRICT?

✓ Документируй обязательность связей

• 1:1 обязательная vs опциональная
• 1:N обязательная vs опциональная

✓ Проверь индексы на FK

• Для каждого JOIN нужен индекс

🚩 Избегай

• Циклических зависимостей
• Polymorph без необходимости
• Множественных FK на одну таблицу

Итого

5 основных типов связей:

1. 1:1 - уникальная ссылка
2. 1:N - самая частая
3. N:M - через junction table
4. Recursive - на саму себя
5. Polymorphic - на разные таблицы (избегай!)

Всегда определяй: ON DELETE и ON UPDATE стратегии. Всегда добавляй: индексы на FK.