Нормализация базы данных на примере учёта заказов интернет-магазина

Введение

Размещение сведений о заказе, покупателе и товаре в одной таблице упрощает первоначальный ввод, но приводит к повторению контактов и названий товаров. Изменение одной копии без изменения остальных создаёт противоречия. Нормализация позволяет уменьшить подобную избыточность [1].

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

Объекты и методы исследования

Рассматривается модель с двумя покупателями, тремя товарами, тремя заказами и пятью позициями. Данные вымышлены. Заказ принадлежит одному покупателю; покупатель может оформить несколько заказов. Один товар встречается в заказе однократно, повторное добавление увеличивает количество. Цена фиксируется для позиции и может отличаться между заказами.

Исходная таблица содержит номер заказа, код покупателя, его электронную почту, код и название товара, количество и цену продажи. Её составной первичный ключ – пара «номер заказа, код товара». Фрагмент данных приведён в таблице 1. Коду 10 соответствует клавиатура, коду 20 – мышь, коду 30 – монитор. Для сокращения ширины таблицы названия и цены описаны в тексте.

Таблица 1

Фрагмент исходной таблицы заказов

Применены анализ функциональных зависимостей и проверка операций в SQLite 3.53.1. Функциональная зависимость означает однозначное определение одного атрибута другим: например, код покупателя определяет его текущую почту. Разделение выполнено по правилам первых трёх нормальных форм [1]. Проверялись восстановление записей и операции изменения данных.

Результаты и их обсуждение

Исходная таблица уже соответствует первой нормальной форме: каждая ячейка содержит одно значение, а строка описывает одну позицию заказа. Вместе с тем электронная почта первого покупателя повторяется три раза. Если изменить её только в строке заказа 102, база будет хранить два разных контакта одного покупателя. Это аномалия обновления. Добавить новый товар без заказа в выбранную структуру также нельзя без фиктивного номера заказа. Удаление всех строк заказа 103 уничтожит единственное упоминание второго покупателя и товара 30.

Для перехода ко второй нормальной форме выделяются атрибуты, зависящие лишь от части составного ключа [1]. Код покупателя зависит от номера заказа, а название товара – от кода товара. Эти сведения переносятся в таблицы заказов и товаров. В таблице позиций остаются номер заказа, код товара, количество и цена продажи. Количество и цена определяются всей парой ключевых полей при принятых условиях модели.

На следующем шаге устраняется транзитивная зависимость: номер заказа определяет покупателя, а покупатель – текущую электронную почту. Контакт переносится в отдельную таблицу покупателей. При заданных функциональных зависимостях полученные отношения находятся в третьей нормальной форме [1]. Их состав приведён в таблице 2.

Таблица 2

Структура базы после нормализации

Внешние ключи обеспечивают соответствие связанных записей, а ограничения NOT NULL и CHECK задают допустимость значений полей [2, с. 227-231]. В модели заказ связан с покупателем, позиция – с заказом и товаром. Обязательные поля не допускают пустых значений, количество положительно, цена неотрицательна. В проверочном соединении SQLite контроль внешних ключей включён командой PRAGMA foreign_keys = ON.

После заполнения четырёх таблиц соединение по ключам восстановило все пять исходных строк с теми же значениями. Замена электронной почты покупателя 1 затронула три строки в исходной таблице и одну строку в таблице покупателей. Это число изменяемых записей, а не число SQL-команд: в обоих случаях достаточно одной команды UPDATE. Новый товар 40 удалось добавить без создания заказа. После удаления позиций заказа 103 и самого заказа покупатель 2 и товар 30 сохранились в своих таблицах. Попытка добавить позицию со ссылкой на несуществующий товар 999 была отклонена.

Цена продажи хранится в позиции заказа сознательно. В заказе 101 клавиатура стоит 2000 руб., а в заказе 102 – 1900 руб.; это разные факты продажи одного товара. Для заказа 101 две мыши по 800 руб. вместе с клавиатурой дают 3600 руб. В заказе 103 мышь стоит 850 руб., монитор – 15000 руб. Стоимость заказа 102 составляет 1900 руб., заказа 103 – 15850 руб. Суммы получены как сумма произведений количества на цену позиции. Перенос единственной цены в справочник товаров исказил бы историю продаж при изменении стоимости.

Электронная почта в модели описывает текущий контакт. Для сохранения адреса доставки или названия товара на дату покупки исторические сведения следует моделировать отдельно. Объём хранения и время запросов не измерялись: полученные результаты характеризуют корректность данных и не доказывают ускорение работы базы.

Заключение

Разделение единой таблицы на четыре связанные таблицы сохранило исходные данные и устранило рассмотренные аномалии. Контакт обновляется в одной записи, товар добавляется независимо от заказа, удаление заказа сохраняет справочники. Цена в позиции обеспечивает корректный расчёт ранее оформленных заказов. Пример пригоден для изучения проектирования реляционных баз данных.