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

Реляционную модель предложил Эдгар Кодд в 1970 году в статье «A Relational Model of Data for Large Shared Data Banks». В основе — математическое понятие отношения, откуда и название: relation, а не «связь между таблицами», как часто думают.

У модели три словаря: математический, стандарта SQL и разговорный. Они описывают одно и то же.

Пример на учебной базе: отношение users имеет степень 5 (пять атрибутов) и кардинальность 10 (десять кортежей). Степень задаётся при проектировании и меняется редко; кардинальность меняется при каждой вставке.

Из математического определения следуют четыре свойства. Каждое имеет прямое практическое последствие.

Нарушение атомарности — самая частая ошибка начинающих. Сравните два варианта хранения городов, в которых работает мастер:

-- Плохо: значение не атомарно
CREATE TABLE masters (
    id     integer PRIMARY KEY,
    name   varchar(100),
    cities varchar(500)   -- 'Москва, Казань, Тверь'
);

-- Найти всех мастеров из Твери — только поиском по подстроке,
-- индекс не работает, «Тверь» найдётся внутри «Тверьская»
SELECT * FROM masters WHERE cities LIKE '%Тверь%';

-- Хорошо: одно значение в одной ячейке
CREATE TABLE master_cities (
    master_id integer NOT NULL REFERENCES masters(id),
    city      varchar(100) NOT NULL,
    PRIMARY KEY (master_id, city)
);

-- Поиск точный и по индексу
SELECT master_id FROM master_cities WHERE city = 'Тверь';

Тип данных — первое и самое дешёвое ограничение целостности. Правильно выбранный тип не даст записать в базу мусор без единой строки кода.

Отдельно про время. timestamp хранит «стену календаря» без пояса, timestamptz — момент времени, приведённый к UTC. Для событий (когда произошёл лайк, когда создан заказ) правильный выбор — timestamptz: он останется корректным при переезде сервера в другой часовой пояс и при переводе часов.

NULL — не ноль и не пустая строка. Это отметка «значение отсутствует»: неизвестно, неприменимо или ещё не задано.

Присутствие NULL превращает привычную логику из двузначной в трёхзначную: выражение может быть истинным, ложным или неопределённым (UNKNOWN).

Практическое правило: WHERE отбирает строки, для которых условие истинно. UNKNOWN — не истина, поэтому такие строки в результат не попадают.

-- Допустим, у части пользователей город неизвестен (city IS NULL)

-- Не вернёт пользователей с city = NULL — и это ожидаемо
SELECT name FROM users WHERE city = 'Moscow';

-- Тоже не вернёт их — а вот это ловушка
SELECT name FROM users WHERE city <> 'Moscow';

-- Правильно, если «город не Москва или неизвестен»
SELECT name FROM users WHERE city IS DISTINCT FROM 'Moscow';

Полезные конструкции для работы с NULL:

-- Подставить значение вместо NULL
SELECT name, COALESCE(city, 'не указан') AS city FROM users;

-- Превратить значение в NULL (обратная операция)
SELECT NULLIF(city, '') FROM users;

-- Проверка на отсутствие — только через IS
SELECT name FROM users WHERE city IS NULL;

Лучший способ избежать этих сложностей — запретить NULL там, где он не нужен, ограничением NOT NULL. В учебной базе так сделано для всех обязательных столбцов: users.name, posts.title, likes.liked_at.

Строки в отношении не дублируются — значит, существует набор атрибутов, однозначно определяющий строку. Это и есть ключ.

Пример. В таблице сотрудников потенциальными ключами являются tab_number (табельный номер), inn и email — каждый уникален. Первичным выбирают один, остальные объявляют UNIQUE:

CREATE TABLE employees (
    tab_number integer      PRIMARY KEY,     -- первичный ключ
    inn        char(12)     UNIQUE NOT NULL, -- альтернативный ключ
    email      varchar(200) UNIQUE,          -- альтернативный ключ
    name       varchar(100) NOT NULL
);

Суррогатный или естественный

Главное решение при проектировании: взять ключом реальный атрибут предметной области или ввести искусственный номер.

Практика сложилась такая: первичный ключ — суррогатный, естественный ключ объявляется как UNIQUE. Так вы получаете стабильные ссылки и при этом не теряете защиту от дубликатов.

Составной ключ уместен, когда строка не существует сама по себе — она осмысленна только как пара. Типичный случай — связующая таблица:

-- Один пользователь состоит в одном сообществе не более одного раза
CREATE TABLE memberships (
    user_id      integer NOT NULL REFERENCES users(id),
    community_id integer NOT NULL REFERENCES communities(id),
    joined_at    date    NOT NULL,
    PRIMARY KEY (user_id, community_id)
);

Внешний ключ — столбец, значения которого обязаны существовать в ключе другой таблицы. Он превращает набор независимых таблиц в связанную базу.

В учебной базе posts.community_id ссылается на communities.id:

CREATE TABLE posts (
    id           integer      PRIMARY KEY,
    community_id integer      NOT NULL REFERENCES communities(id),
    title        varchar(200) NOT NULL,
    score        integer      NOT NULL CHECK (score > 0)
);

Ссылочная целостность — гарантия СУБД, что ссылка никогда не ведёт в пустоту. Она работает в обе стороны:

Поведение при удалении и изменении родительской строки настраивается:

-- Лайки исчезают вместе с постом
CREATE TABLE likes (
    id      integer PRIMARY KEY,
    user_id integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    post_id integer NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    liked_at date   NOT NULL
);

Связь описывает, сколько строк одной таблицы соответствует строкам другой. Видов три.

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

Самая частая связь. Одному сообществу принадлежит много постов; каждый пост принадлежит ровно одному сообществу.

Реализация: внешний ключ ставится на стороне «многих». Столбец community_id — в таблице posts, а не наоборот.

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

Пользователь лайкает много постов; пост собирает лайки многих пользователей. Напрямую в реляционной модели такая связь не выражается — она нарушила бы атомарность.

Реализация: вводится третья, связующая таблица с двумя внешними ключами. Именно этим и является likes:

-- Связующая таблица: users ←→ posts
CREATE TABLE likes (
    id       integer PRIMARY KEY,
    user_id  integer NOT NULL REFERENCES users(id),
    post_id  integer NOT NULL REFERENCES posts(id),
    liked_at date    NOT NULL,
    UNIQUE (user_id, post_id)   -- один пользователь лайкает пост один раз
);

Связь М:М всегда распадается на две связи 1:М. Связующая таблица часто несёт собственные атрибуты — здесь это liked_at, в заказе это было бы количество и цена на момент покупки.

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

Одной строке соответствует не более одной строки в другой таблице. Встречается редко и почти всегда означает, что таблицу разделили намеренно.

Реализация: внешний ключ с ограничением UNIQUE — либо сам внешний ключ является первичным.

-- Профиль пользователя вынесен в отдельную таблицу
CREATE TABLE user_profiles (
    user_id  integer PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    bio      text,
    avatar   bytea
);

Зачем разделять: вынести редко читаемые тяжёлые столбцы, ограничить доступ к чувствительным данным на уровне прав, отделить необязательную часть сущности.

Соберём всё вместе на базе «Социальная сеть», с которой вы будете работать весь курс.

Связи в этой схеме:

Заметьте: likes и views — связующие таблицы с суррогатным первичным ключом id вместо составного. Так тоже делают: суррогатный ключ удобнее, если на связующую таблицу кто-то будет ссылаться. Плата — потеря автоматической защиты от дубликатов, которую пришлось бы вернуть ограничением UNIQUE.

Кодд описал не только модель, но и набор операций над отношениями. Результат каждой операции — снова отношение, поэтому операции можно составлять в цепочки. Отсюда возможность вкладывать запросы друг в друга.

Простой SELECT — это композиция проекции и селекции:

-- π(name, city) от σ(age > 20) над users
SELECT name, city
FROM   users
WHERE  age > 20;

Знать алгебру наизусть не требуется. Важно понимать следствие: SQL — декларативный язык. Вы описываете результат в терминах операций над множествами, а как его получить, решает оптимизатор СУБД. Именно поэтому один и тот же результат можно записать через JOIN, через подзапрос или через EXISTS — и СУБД зачастую выполнит их одинаково.