Связи между таблицами: JOIN и нормализация

Нормализация — способ разнести данные по таблицам так, чтобы каждый факт хранился один раз. JOIN собирает их обратно, склеивая строки с совпавшим ключом.

Одна большая таблица и что с ней не так

Вернёмся к заказам. Самое очевидное решение: одна таблица, в каждой строке всё сразу: номер заказа, имя клиента, его телефон, название услуги, цена, категория. Посмотрите, как она ведёт себя, и сравните с тем же набором данных, разнесённым по таблицам.

SELECT items.title, categories.name
FROM items
JOIN categories
  ON items.category_id = categories.id;

Три беды большой таблицы, и у каждой есть название.

• Аномалия правки. Услуга встречается в трёх заказах, значит, и цена записана в трёх строках. Поменяли в двух и забыли про третью: теперь в базе две цены на одну услугу. И никто не знает, какая верна.
• Аномалия вставки. Завести новую услугу, на которую ещё не было заказов, некуда: любая строка этой таблицы это заказ.
• Аномалия удаления. Клиент отменил единственный заказ, мы удалили строку, и вместе с ней пропали его телефон и всё, что мы о нём знали.

Лекарство называется нормализацией. Идея одна: каждый факт хранится ровно один раз, а остальные места ссылаются на него по ключу. Цена лежит в таблице услуг, телефон в таблице клиентов, а в заказе только пара номеров: «клиент 3, услуга 2».

Нормальные формы словами человека

Для нормализации есть строгие правила, нормальные формы. Вот три первые без математики.

1. Первая (1НФ): в одной ячейке одно значение. Не «телефон: 111, 222» и не столбцы «телефон1, телефон2, телефон3». Если значений несколько, это отдельные строки отдельной таблицы.
2. Вторая (2НФ): сведения об услуге (название, цена) зависят от услуги, а не от заказа. Значит, лежат в таблице услуг. Это правило важно, когда ключ состоит из нескольких столбцов: каждый остальной столбец должен зависеть от всего ключа, а не от его части.
3. Третья (3НФ): столбец не должен зависеть от другого не-ключевого столбца. Название категории зависит от категории, а не от услуги, потому что категорию можно переименовать, и тогда придётся менять название при каждой услуге. Значит, оно лежит в таблице категорий.

Запомнить все три можно присказкой, которую приписывают инженеру Биллу Кенту: данные зависят от ключа, всего ключа и ничего, кроме ключа. Нормальных форм больше, но на практике трёх хватает почти всегда.

JOIN: собрать обратно

Разнесённые данные нужно уметь собирать. Это делает JOIN (соединение). Он берёт строки двух таблиц и склеивает те пары, у которых совпал ключ.

SELECT items.title, categories.name
FROM items
JOIN categories ON items.category_id = categories.id;

Часть после ON называется условием соединения: оно говорит, какой столбец одной таблицы равен какому столбцу другой. Перед именами столбцов пишут имя таблицы с точкой (items.title): когда в обеих таблицах есть столбец id, иначе непонятно, о каком речь. Переключайте в схеме виды JOIN на первой вкладке и смотрите, что остаётся в ответе.

• INNER JOIN (его же пишут просто JOIN) оставляет только совпавшие пары. Услуга без категории из ответа пропадёт: ей не нашлось пары.
• LEFT JOIN оставляет все строки левой таблицы. Где пары нет, справа будет NULL. Это самый полезный вид после обычного: он отвечает на вопрос «чего не хватает».
• RIGHT JOIN то же, но для правой таблицы. Его почти всегда можно заменить на LEFT, поменяв таблицы местами, поэтому его пишут редко. Есть ещё FULL JOIN (сохранить обе стороны) и CROSS JOIN (все пары подряд), но в сайтах они нужны редко.

Фокус, которым пользуются постоянно: как найти строки без пары. Нужен LEFT JOIN и условие WHERE правая_таблица.id IS NULL:

SELECT items.title
FROM items
LEFT JOIN categories ON items.category_id = categories.id
WHERE categories.id IS NULL;

Соединять можно сколько угодно таблиц: за каждой следующей идёт новый JOIN. Связь «многие ко многим» (у человека много услуг, у услуги несколько ведущих) хранят в третьей таблице из пар ключей, и тогда запрос проходит по двум соединениям.

Две ловушки. Первая: если в одной из таблиц ключ повторяется, строки умножаются: JOIN выдаст больше строк, чем было в левой таблице. Вторая: условие в WHERE на столбец правой таблицы после LEFT JOIN выбрасывает строки, где справа NULL, и LEFT JOIN превращается в обычный. Условие на правую таблицу для LEFT JOIN пишут в ON, а в WHERE оставляют только IS NULL.

Когда нормализацию нарушают намеренно

Нормализация это не религия, а способ избежать противоречий. Бывает, что копия данных оправдана:

• Исторический факт. В заказе нужна цена на момент покупки. Если потом цена услуги изменится, старые заказы должны показывать прежнюю. Здесь цена в заказе это не дубликат, а другой факт: «сколько заплатили».
• Скорость чтения. Количество заявок, которое показывают на каждой странице, можно хранить готовым числом, а не считать запросом каждый раз. Цена: число надо обновлять вместе с каждой новой заявкой, иначе оно разойдётся с правдой.
• Поисковая копия. Для поиска строят отдельную структуру со склеенным текстом (урок 6). Это тоже копия, построенная для чтения.

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

Практика

Вы в эпохе 2010 года. Сайты на CMS уже разносят данные по таблицам: записи отдельно, категории отдельно, авторы отдельно, а страница собирается JOIN-ами. Сделайте то же для своего проекта.

В странице четыре поля с SQL. Создайте таблицы categories и people и свяжите с ними items внешними ключами. Добавьте данные из брифа, оставив одну услугу без категории. Напишите запрос с двумя JOIN и запрос с LEFT JOIN, который находит услугу без категории. В последней строке скажите, где у вас оправдана копия данных.

Что было не так с 2010 годом

Многие CMS того времени хранили «всё остальное» в одной таблице вида «владелец, название поля, значение». Так сделаны, например, дополнительные поля записей в WordPress. Это удобно: новое поле заводится без изменения схемы. Но платить приходится соединениями: чтобы собрать одну страницу с десятком полей, таблицу приходится соединять с самой собой столько же раз, и на больших сайтах это тормозит. Гибкость схемы и скорость запросов тянут в разные стороны, и каждая CMS выбирает по-своему. Об этом ещё будет речь в части 8.