Базы данных

Реляционная модель: таблицы, ключи, связи

Цели урока

  • Понимать математические основы реляционной модели Кодда
  • Различать PK и FK, знать когда применять каждый
  • Проектировать связи 1:1, 1:N, N:M без аномалий
  • Применять нормализацию и осознанно решать когда денормализовывать

Предварительные знания

  • Зачем существуют базы данных

Каждый день Instagram обрабатывает 100 миллионов загрузок фотографий. Каждая фотография связана с пользователем, хэштегами, локацией, лайками. Без реляционной модели Кодда вся эта структура превратилась бы в хаос за первые 24 часа работы сервиса.

  • **Банковские системы** - FK-связи между счетами, клиентами и транзакциями гарантируют, что деньги не исчезнут в воздух
  • **E-commerce (Amazon)** - нормализованная схема: products, categories, orders, users - миллиарды строк в связанных таблицах
  • **Авиабронирование** - N:M связь между пассажирами и рейсами через junction table tickets с seat, class, price
  • **Spotify** - плейлисты как N:M между users и tracks через playlist_tracks с позицией и добавленной датой
  • **GitHub** - репозитории как N:M с collaborators через permissions с уровнями доступа

Edgar Codd и рождение реляционной модели

Codd работал в IBM Research, когда написал 11-страничную статью 'A Relational Model of Data for Large Shared Data Banks'. IBM медлила с внедрением - реляционные СУБД угрожали продажам иерархической IMS. Первую реляционную систему создали в Беркли (Ingres, 1974). PostgreSQL - прямой потомок Ingres. Codd получил премию Тьюринга в 1981 году.

Таблицы, строки и столбцы

1970 год. IBM Research, Сан-Хосе. Эдгар Кодд публикует 11-страничную статью - **"A Relational Model of Data for Large Shared Data Banks"**. До этого момента БД были иерархическими или сетевыми: программист обязан был знать физическую структуру хранения, указывать указатели, обходить деревья вручную. Кодд предложил радикальный разрыв: данные - это **таблицы**, операции - это **алгебра**. Декларативный запрос вместо императивного обхода. Через 50 лет PostgreSQL, MySQL, Oracle, SQLite - все стоят на этом фундаменте.

Математический терминПривычное названиеОписание
Relation (отношение)Table (таблица)Набор данных об одном типе сущностей - users, orders, products
Tuple (кортеж)Row (строка)Одна запись - один пользователь, один заказ
Attribute (атрибут)Column (столбец)Одно свойство - email, age, created_at
Domain (домен)Data type (тип данных)Допустимые значения - INTEGER, VARCHAR, TIMESTAMP

**Schema** - описание структуры таблицы: какие столбцы, какие типы, какие ограничения. Schema задаётся при CREATE TABLE и гарантирует, что все строки имеют одинаковую структуру. В этом ключевое отличие от MongoDB, где каждый документ может иметь произвольные поля - удобно для прототипов, проблематично для data integrity в production.

**Основные типы данных в PostgreSQL:** INTEGER, DECIMAL/NUMERIC (точные деньги), VARCHAR(n), TEXT, BOOLEAN, DATE, TIMESTAMP, JSONB (JSON с индексами - гибридный вариант), UUID. JSONB в PostgreSQL - это то, как реляционные БД взяли лучшее от document stores.

В терминологии реляционной модели, что такое 'tuple'?

Primary Key и Foreign Key

Каждая строка в таблице должна быть **уникально идентифицируемой**. Без этого невозможно сказать "обнови вот эту запись" - только "обнови запись где name = 'Alice'", что сломается при первом дубликате имени. **Primary Key (PK)** - столбец или комбинация столбцов, значения которых уникальны и никогда не NULL. В Instagram каждый пост имеет числовой ID - это PK. Telegram-сообщение - тоже PK. Без них адресации не существует.

**Foreign Key (FK)** - столбец, который ссылается на PK другой таблицы. FK - это контракт: нельзя вставить заказ с user_id = 999, если такого пользователя нет. Это referential integrity - гарантия, что данные не превратятся в мусор с висячими ссылками. В production-системах Amazon, где миллиарды строк связаны FK, нарушение этого контракта стоило бы денег - буквально.

**ON DELETE - критический выбор.** Удалили пользователя, а его заказы остались с несуществующим user_id? PostgreSQL по умолчанию запретит удаление (RESTRICT). Варианты: `ON DELETE CASCADE` - удалить заказы вместе с пользователем (опасно для аудита), `ON DELETE SET NULL` - обнулить user_id (заказы сохраняются), `ON DELETE RESTRICT` - запрет. В финансовых системах обычно SET NULL или RESTRICT - нельзя терять историю транзакций.

ОграничениеЧто делаетРеальный пример
PRIMARY KEYУникальный идентификатор строки (UNIQUE + NOT NULL)user.id - адрес каждого пользователя в системе
FOREIGN KEYСсылка на PK другой таблицы, referential integrityorder.user_id - гарантирует что заказ привязан к реальному пользователю
UNIQUEЗначения не повторяются (NULL разрешён)user.email - один email = один аккаунт
NOT NULLЗначение обязательноorder.amount - сумма заказа всегда должна быть
CHECKПроизвольное условиеuser.age > 0 - возраст не может быть отрицательным
DEFAULTЗначение по умолчаниюcreated_at DEFAULT NOW() - автоматическая метка времени

**Natural Key vs Surrogate Key.** Natural Key - реальные данные (email, номер паспорта). Surrogate Key - искусственный ID (SERIAL, UUID). Паспорта перевыдаются. Email меняется при смене имени или компании. UUID стабилен навсегда. Surrogate key как PK + natural key как UNIQUE constraint - стандарт современного production.

Таблица orders имеет столбец user_id с FOREIGN KEY на users(id). Попытка вставить заказ с user_id = 100, но пользователя с id = 100 не существует. Что произойдёт?

Типы связей: 1:1, 1:N, N:M

Связи между таблицами - сердце реляционной модели. Три типа, и неправильный выбор гарантирует аномалии при вставке, обновлении или удалении данных. Instagram хранит likes как N:M между users и posts. Uber хранит активную поездку как 1:1 между driver и trip. LinkedIn хранит должности как 1:N между companies и employees. Все три типа - в production каждый день.

**One-to-Many (1:N)** - самый распространённый тип. Один пользователь - много заказов. Один автор - много статей. FK живёт на стороне «Много».

**One-to-One (1:1)** - один пользователь - один профиль. Применяется для разделения данных: частые поля в основной таблице, редко используемые - в дополнительной. FK + UNIQUE на стороне дочерней таблицы.

**Many-to-Many (N:M)** - студенты и курсы: один студент - много курсов, один курс - много студентов. Реализуется через **junction table** с двумя FK. Spotify: плейлист (N:M) с треками. GitHub: репозитории (N:M) с коллабораторами.

**Как определить тип связи?** Два вопроса: 1) Один A может иметь много B? 2) Один B может иметь много A? Оба да - N:M. Только первый - 1:N. Оба нет - 1:1.

Как реализовать связь N:M между авторами и книгами (один автор - много книг, одна книга - несколько авторов)?

Нормализация: устранение аномалий

**Нормализация** - процесс организации данных в таблицах для устранения дублирования и аномалий. Аномалии возникают когда вставка, обновление или удаление одной записи вызывает непредвиденные side effects. В ненормализованной таблице изменение email у Alice с 5000 заказами требует UPDATE 5000 строк - и при сбое на 3000-й строке в системе появляется два разных email для одного человека.

**1NF (First Normal Form):** каждая ячейка содержит атомарное (неделимое) значение. Нет списков, массивов или вложенных таблиц в ячейке. **2NF:** 1NF + каждый не-ключевой атрибут зависит от ВСЕГО первичного ключа (актуально для составных PK). **3NF:** 2NF + нет транзитивных зависимостей: если A определяет B, а B определяет C - выносить B и C в отдельную таблицу.

**Денормализация** - намеренное нарушение нормальных форм ради производительности. JOIN 5 таблиц на каждый запрос дорог. Иногда стоит хранить `customer_name` прямо в orders, чтобы избежать JOIN. Это trade-off: скорость чтения vs целостность данных. Amazon, Netflix, Twitter используют денормализацию на горячих путях - но осознанно, а не случайно.

**Правило большого пальца:** начинать с 3NF, денормализовать только когда профилировщик показывает что JOIN - bottleneck. Преждевременная денормализация так же вредна, как преждевременная оптимизация.

Данные всегда нужно нормализовать до 3NF - денормализация это плохой дизайн

Денормализация - намеренный trade-off. В read-heavy системах (аналитика, дашборды, поиск) денормализованные данные читаются в 10-100 раз быстрее за счёт отсутствия JOIN.

Нормализация минимизирует дублирование и аномалии обновления - но ценой JOIN при чтении. Если данные читаются в 100 раз чаще чем обновляются (типично для веба), денормализация горячих путей - обоснованное инженерное решение.

Таблица orders содержит: order_id, customer_name, customer_email, product_name, product_price. Alice (5000 заказов) изменила email. Что нужно сделать?

Ключевые идеи

  • **Реляционная модель Кодда (1970)** - данные как таблицы (relations), строки (tuples), столбцы (attributes) со строгой схемой. Именно простота этой модели сделала возможным универсальный язык SQL
  • **Primary Key** уникально идентифицирует строку; **Foreign Key** связывает таблицы и гарантирует referential integrity
  • **Три типа связей:** 1:1 (user - profile), 1:N (user - orders), N:M (students - courses через junction table)
  • **Нормализация** (1NF -> 2NF -> 3NF) устраняет аномалии, но денормализация допустима для read-heavy сценариев
  • **Surrogate keys** (SERIAL, UUID) надёжнее Natural keys - email меняется, UUID нет
  • ON DELETE CASCADE vs SET NULL vs RESTRICT - осознанный выбор исходя из требований к аудиту и целостности

Связанные темы

Реляционная модель - фундамент для понимания SQL и system design:

  • ACID: четыре столпа надёжности — Транзакции обеспечивают целостность при параллельном доступе к связанным таблицам
  • Зачем существуют базы данных — Вводный урок: файлы vs БД, типы БД, CRUD, client-server модель
  • Hash Tables — Hash indexes в БД используют те же принципы, что и in-memory hash tables

Вопросы для размышления

  • В последнем проекте - какие связи были между сущностями? 1:1, 1:N или N:M?
  • В каких случаях стоит выбрать UUID вместо SERIAL (auto-increment) как Primary Key?
  • Если таблица имеет 500 миллионов строк и 20 столбцов - как денормализация может ускорить аналитические запросы?

Связанные уроки

  • db-01-intro — Базовое понятие СУБД и зачем БД вообще нужны
  • db-03-acid — ACID-транзакции работают поверх реляционной модели
  • pg-04-ddl — CREATE TABLE, ALTER, DROP - реляционная модель в PostgreSQL-синтаксисе
  • db-09-indexes-btree — Индексы по PK и FK - следующий шаг после понимания ключей
  • ds-23-btree — B-tree структура данных лежит под индексами PK в реляционных БД
  • isd-08-database-selection
  • ds-11-bst
Реляционная модель: таблицы, ключи, связи

0

1

Войти