Основы реляционных баз данных

Первая нормальная форма

Возьмем для примера обычный интернет-магазин, в котором продается электроника. Каждый раз когда пользователь делает заказ, в базу данных заносится запись об этом (в таблицу order_items). В нее входит вся необходимая информация: какой товар купил пользователь, сколько он стоил, адрес доставки и другое. Затем эти данные используются всеми подразделениями интернет-магазина, начиная от бухгалтеров, заканчивая службой доставки.

order_items

first_name	last_name	address	item	price
Сергей	Иванов	Москва, ул. Промышленная	утюг	15.00
Иван	Петров	Самара, ул. Энгельса	кофеварка	5000.00
Виктор	Сидоров	Омск, ул. Дворцовая	утюг, телевизор	1000.00, 6500.00
Сергей	Иванов	Москва, ул. Матросова	ноутбук	20000.00
Сергей	Иванов	Москва, ул. Матросова	ноутбук	20000.00

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

Невооруженным взглядом видно, что в этой табличке много повторяющейся информации. Попробуем привести ее к правильной структуре с точки зрения реляционной модели. Для этого пройдемся по нормальным формам.

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

• Каждая ячейка таблицы может хранить только одно значение
• Все данные в одной колонке могут быть только одного типа
• Каждая запись в таблице должна однозначно отличаться от других записей

Каждая ячейка – одно значение

В примере выше, у одной записи, поля item и price содержат два значения, разделенных запятой. Такой способ организации данных имеет множество недостатков. Например пропадает возможность делать обычную выборку по условиям:

-- Как найти записи о всех проданных утюгах?
SELECT * from order_items WHERE item = "?"

Другая проблема связана с типами данных. Поле прайс в таблице order_items имеет числовой тип (numeric). Если мы захотим хранить там более одного значения, то тип превратится в строковый, а все данные станут обычными строками. При такой организации, невозможно проверить корректность данных, формат числа. Становится крайне проблематично выполнить агрегирующие запросы, например, посчитать выручку за определенный месяц одним запросом.

Избавиться от перечислений в ячейках можно через создание новых записей:

first_name	last_name	address	item	price
Сергей	Иванов	Москва, ул. Промышленная	утюг	15.00
Иван	Петров	Самара, ул. Энгельса	кофеварка	5000.00
Виктор	Сидоров	Омск, ул. Дворцовая	утюг	1000.00
Виктор	Сидоров	Омск, ул. Дворцовая	телевизор	6500.00
Сергей	Иванов	Москва, ул. Матросова	ноутбук	20000.00
Сергей	Иванов	Москва, ул. Матросова	ноутбук	20000.00

Данные одного типа

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

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

Вот еще несколько примеров, с похожей ситуацией:

• Хранение даты свадьбы в поле "день рождения"
• Хранение телефона вместо адреса в поле "адрес"

Исправленная версия таблицы:

first_name	last_name	address	item	price
Сергей	Иванов	Москва, ул. Промышленная	утюг	1000.00
Иван	Петров	Самара, ул. Энгельса	кофеварка	5000.00
Виктор	Сидоров	Омск, ул. Дворцовая	утюг	1000.00
Виктор	Сидоров	Омск, ул. Дворцовая	телевизор	6500.00
Сергей	Иванов	Москва, ул. Матросова	ноутбук	20000.00
Сергей	Иванов	Москва, ул. Матросова	ноутбук	20000.00

Уникальные записи

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

Реляционная модель требует от нас уникальности каждой записи. Для чего это нужно? Иначе невозможно понять что к чему относится и с какой записью нужно работать при изменениях. Очень легко начать править не то и потерять важную информацию. Причем мы даже не можем полагаться на порядок данных внутри таблицы, ведь он не гарантирован.

Реализовать уникальность можно несколькими способами, например, добавить новых полей, которые сделают запись уникальной, например дату заказа. Этот способ не очень надежный, а главное не очень удобный в работе. Придется постоянно анализировать весь набор полей. Гораздо лучше добавить первичный ключ.

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

В качестве первичного ключа можно использовать какое-то значение из окружающего мира, например email или ФИО, но нужно однозначно убедиться что ключ не будет повторяться. Такой первичный ключ называется естественным. Естественные ключи используют редко из-за их ненадежности. Почти наверняка они не уникальны и могут изменяться или повторяться. Например, номер паспорта меняется при смене паспорта.

Другой подход основан на использовании автоматически генерируемых уникальных значений. Такой первичный ключ называется суррогатным и поддерживается любой базой данных "из коробки". Иногда это просто числа, а иногда и сложные число-буквенные строки (хеши). Добавим в нашу таблицу первичный ключ:

id	first_name	last_name	address	item	price
8	Сергей	Иванов	Москва, ул. Промышленная	утюг	1000.00
2	Иван	Петров	Самара, ул. Энгельса	кофеварка	5000.00
7	Виктор	Сидоров	Омск, ул. Дворцовая	утюг	1000.00
4	Виктор	Сидоров	Омск, ул. Дворцовая	телевизор	6500.00
9	Сергей	Иванов	Москва, ул. Матросова	ноутбук	20000.00
6	Сергей	Иванов	Москва, ул. Матросова	ноутбук	20000.00

Первичный ключ принято создавать первым полем с названием id. Для первичного ключа обязательно указывать PRIMARY KEY в описании таблицы:

-- Первичный ключ только один на таблицу
CREATE TABLE products (
    id bigint PRIMARY KEY,
    first_name varchar(255),
    last_name varchar(255),
    address varchar(255),
    item varchar(255),
    price numeric -- специальный тип данных, подходящий под работу с деньгами. Обеспечивает высокую точность при расчетах.
);

Такой ключ все еще нужно формировать самостоятельно (с автогенерацией мы познакомимся позже), но теперь база данных сама следит за уникальностью. При попытке создать запись с повторяющимися первичными ключами возникнет ошибка.

---

Дополнительные материалы

1. Первичный ключ
2. Внешний ключ
3. Суррогатный ключ

---