Нормализация

Введение: Порядок в данных

Представьте, что вы ведете список сотрудников в Excel. Вы начинаете с простой таблицы: имя, телефон, отдел. Потом вам нужно добавить проект, над которым работает сотрудник. Вы добавляете столбец “Проект”. Но сотрудник может работать над несколькими проектами. Вы начинаете дублировать строки. Потом сотрудник переходит в другой отдел – и вы должны обновить отдел во всех строках. Вы ошибаетесь, данные расходятся, начинается хаос.

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

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

Простыми словами: она учит вас раскладывать данные по полочкам так, чтобы каждая информация хранилась в одном месте, не дублировалась и не противоречила сама себе.

Почему нужна нормализация: Проблемы плохой структуры (аномалии)

Давайте рассмотрим таблицу, которая НЕ нормализована. Представьте, что мы храним все данные об отгрузках в одной таблице.

Таблица “Отгрузки” (плохая):

Какие здесь проблемы?

Проблема 1: Избыточность (дублирование) Информация о поставщике (“ООО “Компьютеры””, “Москва”, “74951234567”) повторяется в каждой строке с товарами этого поставщика. Если поставщиков много и отгрузок много, дублирование будет огромным.

Проблема 2: Аномалия обновления (update anomaly) Что будет, если у “ООО “Компьютеры”” изменится телефон? Придется обновлять все строки, где есть этот поставщик. Если вы пропустите хотя бы одну строку, в базе будут противоречивые данные – два разных телефона для одного поставщика.

Проблема 3: Аномалия вставки (insert anomaly) Как добавить нового поставщика, который пока не поставил ни одного товара? Придется создавать строку с пустыми полями product_name, quantity, shipment_date. Это не имеет смысла.

Проблема 4: Аномалия удаления (delete anomaly) Если удалить последнюю отгрузку поставщика “ООО “Комплект””, мы потеряем всю информацию об этом поставщике. Его адрес и телефон исчезнут из базы.

Нормализация решает все эти проблемы.

Основные идеи нормализации

Прежде чем мы перейдем к конкретным правилам, запомните две главные идеи.

Идея 1: Одна сущность – одна таблица Каждая таблица должна описывать одну сущность (один тип объектов). Нельзя смешивать в одной таблице “Отгрузки” и “Поставщиков” – это разные сущности.

Идея 2: Каждая информация хранится в одном месте Телефон поставщика хранится в таблице “Поставщики”, а не в каждой отгрузке. Если телефон изменится, вы обновите его в одном месте.

Эти идеи звучат просто. Но на практике их легко нарушить.

Для примеров мы возьмем учебную систему со студентами, курсами и преподавателями:

Том посещает курс по математике, который преподает Смит. Дата записи 11/06/2017.
Сэм посещает курс по алгоритмам, который преподает Адамс. Дата записи 12/06/2017.
Боб посещает курс по математике, который преподает Смит. Дата записи 13/06/2017.
Том посещает курс по языку JavaScript, который преподает Адамс. Дата записи 14/06/2017.
Сэм имеет два электронных адреса: sam@gmail.com и sam@hotmail.com.
В университете может быть только один курс с определенным именем. Один преподаватель может преподавать несколько курсов.

Первая нормальная форма (1НФ)

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

Что запрещено:

• Поле product_ids со значением “101,102,103”.

• Столбцы Course1, Course2, Course3 (повторяющиеся группы).

• Поле Emails с несколькими адресами через запятую.

Плохой пример (нарушает 1НФ):

Возьмем ненормализованную таблицу StudentCourses, которая содержит всю информацию о студентах, курсах и email-ах.

Здесь две проблемы:

1. Повторяющаяся группа атрибутов: Course1, Date1, TeacherId1, Teacher1 и Course2, Date2, TeacherId2, Teacher2. Это группа атрибутов, которая описывает один курс. Если студент запишется на десять курсов, пришлось бы добавить десять таких групп.

2. Неатомарное значение: В столбце Emails у Сэма два адреса в одной ячейке.

Решение: два подхода к переходу к 1НФ

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

Теперь у нас нет повторяющихся столбцов, но увеличилась избыточность (имя Том повторяется). Также теперь первичный ключ – составной: (StudentId, CourseId).

Подход 2 (для списков email-ов): вынесение в отдельную таблицу
Атрибут Emails содержит набор значений. Выносим его в отдельную таблицу вместе с копией ключа (StudentId).

Таблица “Emails”:

Таблица “StudentCourses” после удаления столбца Emails:

Важное наблюдение для аналитика:

• Если повторяющаяся группа содержит уникальные значения для каждой строки (как email-ы), мы имеем дело со связью “один-ко-многим” (один студент – много email-ов).

• Если повторяющаяся группа содержит неуникальные значения, которые могут повторяться в разных строках (как атрибуты курсов), мы имеем дело со связью “многие ко многим” (много студентов – много курсов).

Итог 1НФ: Теперь в каждой ячейке — одно значение, нет повторяющихся групп столбцов.

Вторая нормальная форма (2НФ)

Требование: Таблица должна быть в 1НФ, и каждый неключевой столбец должен зависеть от всего первичного ключа, а не от его части. Это требование актуально только для таблиц с составным первичным ключом.

Ключевое понятие: функциональная зависимость

• Полная функциональная зависимость: Атрибут “B” полностью зависит от атрибута “A”, если “B” зависит от всего значения “A”, а не от его части.

• Частичная функциональная зависимость: Атрибут “B” зависит только от части составного ключа.

Плохой пример (нарушает 2НФ):

Наша таблица StudentCourses после 1НФ имеет составной первичный ключ (StudentId, CourseId). Какие зависимости?

Наличие частичных зависимостей означает, что таблица не во 2НФ.

Решение: разбить таблицу на несколько

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

Таблица “Students” (зависит от StudentId):

Таблица “Courses” (зависит от CourseId):

Таблица “StudentCourses” (связующая, зависит от всей пары):

Итог 2НФ: Между таблицами Students и Courses образовалась связь “многие-ко-многим” через таблицу StudentCourses. Теперь имя студента и название курса хранятся в одном месте.

Третья нормальная форма (3НФ)

Требование: Таблица должна быть в 2НФ, и каждый неключевой столбец должен зависеть только от первичного ключа, а не от других неключевых столбцов. Другими словами, не должно быть транзитивных функциональных зависимостей.

Транзитивная зависимость – это косвенная связь между атрибутами в таблице, которая приводит к функциональной зависимости, то есть когда одно значение атрибута однозначно определяет другое. Проще говоря, это ситуация, когда информация о третьем атрибуте может быть получена через информацию о втором, а первый атрибут “транзитивно” определяет третий. Это ситуация, когда A → B и B → C. Атрибут “C” транзитивно зависит от “A”. При этом A функционально не зависит ни от “B”, ни от “C”.

Плохой пример (нарушает 3НФ):

Посмотрим на таблицу Courses, которую мы получили после 2НФ.

Какие зависимости?

• CourseId → Course, TeacherId, Teacher

• Course → CourseId, TeacherId, Teacher (название курса уникально, это потенциальный ключ)

• TeacherId → Teacher

Здесь есть транзитивная зависимость: CourseId → TeacherId и TeacherId → Teacher. Получается, Teacher зависит от CourseId через TeacherId.

Решение: вынести в отдельную таблицу

Создаем таблицу Teachers и убираем из Courses все, что зависит от преподавателя.

Таблица “Teachers”:

Таблица “Courses” после изменений:

Итог 3НФ: Теперь телефон, должность или любая другая информация о преподавателе будет храниться в таблице Teachers в одном месте. Если у Адамса изменится должность, мы обновим одну строку.

А что такое денормализация?

Денормализация – это намеренное нарушение правил нормализации ради производительности.

Иногда, чтобы ускорить чтение данных, разработчики специально добавляют дублирующие поля. Например, в таблицу “Заказы” добавляют customer_name, чтобы не делать JOIN (объединение) с таблицей “Клиенты” при каждом запросе.

Цена денормализации:

• Нужно синхронизировать дублирующиеся данные (если клиент сменил имя, обновить во всех заказах).
• Больше места.
• Риск рассинхрона.

Когда допустима: Очень большие нагрузки на чтение, когда скорость критичнее, чем простота поддержки.

Для СА: Нормализация – это “базовый” правильный подход. Денормализация – это осознанное отступление от правил, когда есть веские причины. Не денормализуйте “просто так”.

Проблемы и решения

Резюме для системного аналитика

1. Нормализация – это устранение дублирования и аномалий. Одна информация – одно место хранения.

2. Три нормальные формы – это практический минимум.

   • 1НФ: Атомарные значения, нет повторяющихся групп.
   • 2НФ: Зависимость от всего составного ключа.
   • 3НФ: Зависимость только от первичного ключа (нет транзитивных зависимостей).

3. Проблемы, которые решает нормализация: избыточность, аномалии обновления, вставки, удаления.

4. Нормализованная структура проще в поддержке. Обновили телефон поставщика в одном месте – все отгрузки “увидят” новые данные через связь.

5. Денормализация – это осознанное нарушение правил ради производительности. Не делайте это без веской причины.