Нормализация баз данных

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

Аномалии данных

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

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

Аномалии обновления

Аномалия обновления возникает, когда все копии одной и той же информации не обновляются правильно. Рассмотрим, например, следующую таблицу:



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



Информация в таблице по-прежнему может считаться точной. Однако запрос к базе данных, направленный на получение информации обо всех разработчиках, стал сложнее. Хуже всего будет, если следующее обновление , изменяющее должность Боба обратно на «Разработчик», будет выполнено только для одной из двух его записей, особенно если новая должность написана с ошибкой:



Аномалии вставки

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



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



Аномалия удаления

Аномалия удаления возникает, когда удаление информации из таблицы приводит к непреднамеренным последствиям, как правило, из-за того, что структура таблицы затрудняет удаление данных. Рассмотрим ситуацию, когда компания, использующая таблицу из примера, решила, что ей больше не потребуются навыки C#. Если удалить все ссылки на C#, Джим полностью исчезнет из базы данных. Аналогично, если Пэт уйдет из компании, удаление ее записи приведет к удалению навыка PRINCE2 из базы данных.

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

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

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

Нормализацию следует применять к базам данных, которые часто обновляются, но редко запрашиваются. Добавление таблиц в базу данных обычно увеличивает количество таблиц, которые необходимо объединить для выполнения запроса. Поскольку каждое объединение увеличивает нагрузку на запрос, снижая производительность, возможна чрезмерная нормализация базы данных. Это особенно заметно для баз данных, которые используются только для целей отчетности. В таких ситуациях обычно проводится денормализация (US: denormalize) базы данных, вводя дублирование, которое увеличивает производительность запросов.

Нормальные формы

Нормальные формы реляционных баз данных описывают уровень уязвимости конкретной конструкции к проблемам целостности данных. Они определяют набор правил, которые следует применять при проектировании базы данных. Каждая форма имеет порядковый номер, причем «первая нормальная форма» является наименее нормализованной и наиболее уязвимой к аномалиям. Более высокие порядковые номера указывают на все более строгие правила. Кроме того, существуют некоторые названные нормальные формы, такие как нормальная форма Бойса-Кодда, которые применяют дополнительные правила.

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

Первоначально Эдгар Ф. Кодд описал первую, вторую и третью нормальные формы. Эти три формы являются наиболее распространенными и всегда достижимыми при проектировании баз данных. Они будут описаны в остальных разделах этой статьи. Существуют и другие нормальные формы, в том числе нормальная форма Бойса-Кодда, а также четвертая, пятая и шестая нормальные формы. Однако эти более строгие формы не всегда достижимы.

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

Чтобы таблица соответствовала первой нормальной форме (1NF), она должна удовлетворять определенным условиям. Основные правила:

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

Эти правила можно проиллюстрировать на примере некоторых таблиц.

Пример 1



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

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



Пример 2



Во втором примере мы возвращаемся к списку сотрудников. В этом случае разработчик базы данных создал столбец «Навыки», содержащий перечень навыков каждого сотрудника. Если у сотрудника несколько навыков, они разделены запятыми. Это нарушает правило, согласно которому каждый столбец каждой строки содержит ровно один элемент данных.

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

Таблица сотрудников



Таблица навыков сотрудников



Вторая нормальная форма

Для соответствия второй нормальной форме (2НФ) таблица должна удовлетворять всем требованиям первой нормальной формы (1НФ). Кроме того, все столбцы, не являющиеся простыми числами, то есть столбцы, не входящие в состав потенциального ключа, должны зависеть от целых потенциальных ключей, а не только от их частей. Это правило применяется только к таблицам, содержащим составные ключи. Если все возможные потенциальные ключи состоят только из отдельных столбцов, это правило не действует.

Пример



В этом примере в таблицу «Навыки сотрудников» добавлен столбец для хранения заработной платы сотрудника. Этот столбец нарушает правило 2NF, поскольку заработная плата зависит от сотрудника, а не от его навыков. Это лишь один столбец из двухстолбцового составного ключа.

Для изменения структуры данных с целью достижения 2NF, столбец «Заработная плата» следует удалить из таблицы «Навыки сотрудников» и добавить его в таблицу «Сотрудники». Это также позволит избежать дублирования данных о заработной плате, которое может привести к аномалиям при обновлении.

Таблица сотрудников



Таблица навыков сотрудников



Третья нормальная форма

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

Пример



В приведенном выше примере разработчик создал новую таблицу, которая связывает каждого сотрудника с местоположением офиса и основным телефонным номером этого офиса. Эта таблица соответствует всем требованиям для 2NF. Единственным подходящим ключом для таблицы является столбец Сотрудник. Столбец "Основной офис" напрямую зависит от этого ключа. Однако столбец "Телефон офиса" напрямую не связан с сотрудником. Он связан с сотрудником транзитивно через основной офис. Таким образом, эта таблица не соответствует требованиям для 3NF.

Для достижения 3NF таблица должна быть разделена на две таблицы. В первой таблице будет указано местоположение сотрудника. Во второй будут указаны подробные данные о каждом местоположении, включая номер телефона. Чтобы выполнить запрос, который возвращает имя и номер телефона сотрудника, необходимо объединить таблицы. Это решение устраняет риск возникновения ошибок при обновлении, когда один и тот же офис отображается для нескольких сотрудников, но номер телефона изменяется только для одного из них.

Таблица офисов сотрудников



Таблица офисов



Ограничения

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