Описание основных приемов нормализации базы данных
В данной статье, ориентированной на начинающих, объясняется терминология нормализации баз данных. Понимание этой терминологии помогает вести разговор об архитектуре и проектировании реляционных баз данных.
Описание нормализации
Нормализация — это процесс организации данных в базе данных, Она включает в себя создание таблиц и установление связей между ними в соответствии с правилами, разработанными как для защиты данных, так и для повышения гибкости базы данных, устраняя избыточность и несогласованную зависимость.
Избыточность данных приводит к непродуктивному расходованию свободного места на диске и затрудняет обслуживание баз данных. Например, если данные, хранящиеся в нескольких местах, потребуется изменить, в них придется внести одни и те же изменения во всех этих местах. Изменение адреса клиента проще реализовать, если эти данные хранятся только в таблице Customers и нигде в базе данных.
Что такое «несогласованные зависимости»? Хотя пользователю интуитивно понятно искать в таблице Клиенты адрес конкретного клиента, возможно, не имеет смысла искать там зарплату сотрудника, который обращается к данному клиенту. Зарплата сотрудника связана с сотрудником (зависит от него), поэтому эти сведения следует хранить в таблице Employees (сотрудники). Несогласованные зависимости могут затруднять доступ к данным, так как путь к данным при этом может отсутствовать или быть неправильным.
Существует несколько правил нормализации баз данных. Каждое правило называется "обычной формой". Если соблюдается первое правило, база данных, как говорят, находится в "первой нормальной форме". При соблюдении первых трех правил база данных считается в "третьей нормальной форме". Хотя и другие уровни нормализации возможны, третья нормальная форма считается самым высоким уровнем, необходимым для большинства приложений.
Как и во многих формальных правилах и спецификациях, реальные сценарии не всегда позволяют обеспечить идеальное соответствие требованиям. Как правило, для выполнения нормализации приходится создавать дополнительные таблицы, и некоторые клиенты считают это нежелательным. Собираясь нарушить одно из первых трех правил нормализации, убедитесь в том, что в приложении учтены все связанные с этим проблемы, такие как избыточность данных и несогласованные зависимости.
В описаниях ниже приведены соответствующие примеры.
Первая нормальная форма
- Устраните повторяющиеся группы в отдельных таблицах.
- Создайте отдельную таблицу для каждого набора связанных данных.
- Идентифицируйте каждый набор связанных данных с помощью первичного ключа.
Не используйте несколько полей в одной таблице для хранения похожих данных. Например, для слежения за товаром, который закупается у двух разных поставщиков, можно создать запись с полями, определяющими код первого поставщика и код второго поставщика.
Что произойдет при добавлении третьего поставщика? Добавление поля не является ответом; он требует изменений в программе и таблице и не обеспечивает плавное размещение динамического числа поставщиков. Вместо этого можно поместить все сведения о поставщиках в отдельную таблицу Vendors (поставщики) и связать товары с поставщиками с помощью кодов товаров или поставщиков с товарами с помощью кодов поставщиков.
Вторая нормальная форма
- Создайте отдельные таблицы для наборов значений, относящихся к нескольким записям.
- Свяжите эти таблицы с помощью внешнего ключа.
Записи не должны зависеть от чего-либо, кроме первичного ключа таблицы (составного ключа, если это необходимо). Возьмем для примера адрес клиента в системе бухгалтерского учета. Этот адрес необходим не только таблице Customers, но и таблицам Orders, Shipping, Invoices, Accounts Receivable и Collections. Вместо того чтобы хранить адрес клиента как отдельный элемент в каждой из этих таблиц, храните его в одном месте: или в таблице Customers, или в отдельной таблице Addresses.
Третья нормальная форма
- Исключите поля, которые не зависят от ключа.
Значения в записи, которые не являются частью ключа этой записи, не принадлежат в таблице. Если содержимое группы полей может относиться более чем к одной записи в таблице, попробуйте поместить эти поля в отдельную таблицу.
Например, в таблицу Employee Recruitment (наем сотрудников) можно включить адрес кандидата и название университета, в котором он получил образование. Однако для организации групповой почтовой рассылки необходим полный список университетов. Если сведения об университетах будут храниться в таблице Candidates, составить список университетов при отсутствии кандидатов не получится. Таким образом, создайте вместо этого отдельную таблицу Universities и свяжите ее с таблицей Candidates при помощи ключа — кода университета.
ИСКЛЮЧЕНИЕ: Придерживаться третьей нормальной формы, хотя теоретически желательно, не всегда является практическим. Например, для устранения всех возможных зависимостей между полями таблицы Customers придется создать отдельные таблицы для хранения сведений о городах, почтовых индексах, торговых представителях, категориях клиентов и любых других сведений, которые могут дублироваться в нескольких записях. Теоретически нормализация стоит проводить. Однако значительное увеличение числа маленьких таблиц может привести к снижению производительности СУБД или исчерпанию памяти и числа дескрипторов открытых файлов.
Выполнять нормализацию до третьей нормальной формы может быть целесообразно только для часто изменяемых данных. Если при этом сохранятся зависимые поля, спроектируйте приложение так, чтобы при изменении одного из этих полей пользователь должен был проверить все связанные поля.
Другие формы нормализации
Четвертая нормальная форма, также называемая Boyce-Codd нормальной форме (BCNF), и пятая нормальная форма существуют, но редко рассматриваются в практическом проектировании. Игнорирование этих правил может привести к не совсем идеальному дизайну базы данных, но не должно влиять на функциональные возможности.
Нормализация таблицы примеров
Ниже приведен пример нормализации таблицы с вымышленными данными о студентах.
Таблица до нормализации:
Student# Advisor Adv-Room Class1 Class2 Class3 1022 Петров 412 101-07 143-01 159-02 4123 Иванов 216 101-07 143-01 179-04 Первая нормальная форма: нет повторяющихся групп
Таблицы должны иметь только два измерения. Так как один студент изучает несколько курсов, эти курсы следует указать в отдельной таблице. Наличие полей Class1, Class2 и Class3 в приведенных выше записях свидетельствует о неудачном проектировании таблицы.
В электронных таблицах часто используется третье измерение, но таблицы не должны. Другой способ взглянуть на эту проблему заключается в отношениях "один ко многим", не помещайте одну сторону и много сторон в одну таблицу. Вместо этого создайте другую таблицу в первой обычной форме, исключив повторяющуюся группу (Class#), как показано в следующем примере:
Student# Advisor Adv-Room Class# 1022 Петров 412 101-07 1022 Петров 412 143-01 1022 Петров 412 159-02 4123 Иванов 216 101-07 4123 Иванов 216 143-01 4123 Иванов 216 179-04 Вторая нормальная форма: устранение избыточных данных
Обратите внимание на несколько значений Class# для каждого значения Student# в таблице выше. Класс# функционально не зависит от Student# (первичного ключа), поэтому эта связь не является второй нормальной.
В следующей таблице представлена вторая нормальная форма:
Таблица Students:
Student# Advisor Adv-Room 1022 Петров 412 4123 Иванов 216 Таблица Registration:
Student# Class# 1022 101-07 1022 143-01 1022 159-02 4123 101-07 4123 143-01 4123 179-04 Третья нормальная форма: устранение данных, не зависящих от ключа
В последнем примере значения Adv-Room (номер кабинета научного руководителя) функционально зависят от атрибута Advisor. Решить эту проблему можно, переместив данный атрибут из таблицы Students в таблицу Faculty (факультет):
Таблица Students:
Student# Advisor 1022 Петров 4123 Иванов Таблица Faculty:
Имя Room Dept Петров 412 42 Иванов 216 42