Вперед к основам: 1NF, 2NF, 3NF

Учитель сказал:
- Учиться и время от времени повторять изученное, разве это не приятно?

Конфуций, "Лунь Юй"

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

В 1970 - 1971 годах Эдгар Кодд впервые сформулировал требования к нормализованным структурам данных, названные им нормальная форма 1, 2 и 3 (1NF, 2NF, 3NF). С тех пор были определены еще несколько нормальных форм, которые предъявляют дополнительные требования к организации данных в реляционной БД; о них можно прочитать, например, в статье Википедии.

Однако, в большинстве случаев достаточно обеспечить соответствие реляционной структуры данных 3-ей нормальной форме (3NF). Далее будут рассмотрены первые три нормальные формы, примеры их нарушения, сопутствующие этому проблемы и способы их решения.

1-я нормальная форма (1NF)

Таблица БД соответствует 1NF, когда в строке таблицы нет повторяющихся элементов или групп элементов.

В следующей таблице, нарушающей 1NF, одна строка представляет заказ, содержащий один или более заказываемых элементов в столбцах ItemId1, ..., ItemIdN:

Table ORDER
-----------
OrderId ItemId1  ItemId2  ItemId3  ...
      1     100      101      102  

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

Неформально, нарушение 1NF уподобляет строки таблицы записям переменной длины. Это существенно затрудняет построение SQL запросов.

Для приведения структуры данных к 1-ой нормальной форме нужно выделить заказываемые товары в отдельную таблицу ORDER_ITEM, где столбец OrderId ссылается на ORDER:

Table ORDER
-----------
OrderId ...
      1

Table ORDER_ITEM
----------------
OrderId ItemId ...
      1    100
      1    101
      1    102

Главная цель 1-ой нормальной формы - обеспечение возможности эффективно манипулировать данными с помощью SQL.

Еще примеры с нарушениями 1NF:

Table ARTICLE
-------------
ArticleId Name     CategoryId1 CategoryId2 CategoryId3
        1 Ноутбук            5          13          33
        2 Блокнот           13          17            

Насколько просто узнать, относится ли артикул к интересующей нас категории? хотя бы к одной категории? Как выбрать все артикулы, относящиеся к интересующей нас категории (например, 13)? Что делать, если артикул относится более чем к трем категориям?

Аналогичные вопросы можно задать для следующих таблиц.

Таблица USERS предусматривает принадлежность пользователя не более чем к 3 группам:

Table USERS
-----------
UserId Name        GroupId1 GroupId2 GroupId3
     9 Андрей                      2
    10 Иван               2        3       17

Таблица AUTHOR позволяет автору иметь не более 2 псевдонимов:

Table AUTHOR
------------
AuthorId Name        Alias1      Alias2
       1 Ульянов     Ленин       Николаев
       2 Пешков      Горький

2-я нормальная форма (2NF)

Таблица БД соответствует 2NF, если она соответствует 1NF и, дополнительно, в таблице нет зависимостей от части составного ключа.

Следующая таблица, нарушающая 2NF, содержит заказы, причем один заказ в ней представлен одной или более строками:

Table ORDER
-----------
   (PK)   (PK)
OrderId ItemId  OrderDate  ...
      1    100  2013-01-01
      1    101  2013-01-01

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

Здесь OrderDate зависит от OrderId и не зависит от ItemId. Значение OrderDate всегда должно быть одно и то же для каждого данного OrderId, независимо от значения столбца ItemId. Имеющее место дублирование (размножение) данных свидетельствует о денормализации.

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

При необходимости изменить OrderDate для заказа 1 придется проапдейтить... сколько строк? А в хорошо спроектированной (нормализованной) базе данных достаточно проапдейтить только одну.

Проблема решается таким образом:

Table ORDER
-----------
   (PK)
OrderId  OrderDate  ...
     1   2013-01-01

Table ORDER_ITEM
----------------
   (PK)    (PK)
OrderId  ItemId  ...
      1     100
      1     101

Еще примеры нарушения 2NF, влекущие аналогичные проблемы:

Table BRAND
-----------
   (PK)      (PK)
BrandId ArticleId BrandName
      1        12 Столичная
      1        59 Столичная

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

Table ROLE
----------
  (PK)   (PK)
RoleId UserId  RoleName
     1      5  Administrator
     1      7  Administrator

3-я нормальная форма (3NF)

Таблица БД соответствует 3NF, если она соответствует 2NF и, дополнительно, в таблице нет зависимостей от неключевых столбцов.

Table ORDER
-----------
   (PK)
OrderId  OrderDate   CustomerName  CustomerCity
      1  2013-01-01  Иван Иванов   Владивосток
     33  2013-02-20  Иван Иванов   Владивосток

Здесь CustomerCity зависит от CustomerName.

Для того, чтобы изменить город клиента "Иван Иванов" или его имя, придется проапдейтить... сколько строк? Должно быть достаточно одной.

Если понадобится добавить потенциального клиента, у которого пока нет заказов, этого просто нельзя будет сделать. Такая проблема известна как аномалия добавления (insertion anomaly).

Если удалить заказы данного клиента, то данные о клиенте будут потеряны. Такая проблема известна как аномалия удаления (deletion anomaly).

Столбцы, которые не добавляют фактов, описывающих первичный ключ, необходимо убрать из таблицы. В рассматриваемом примере CustomerCity и CustomerName выносятся в таблицу CUSTOMER.

Table CUSTOMER
--------------
      (PK)
CustomerId  CustomerName  CustomerCity
         1  Иван Иванов   Владивосток

Table ORDER
-----------
   (PK)
OrderId  OrderDate  CustomerId
      1 2013-01-01           1
     33 2013-02-20           1

Еще примеры нарушения 3NF:

Table COUNTRY
-------------
     (PK)
CounrtyId Name    CurrencyId CurrencyAbbr
      840 США            840 USD
      591 Панама         840 USD

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

Table EMPLOYEE
--------------
      (PK)
EmployeeId Name         Location PhoneNo
         1 Иванов       F3       112
         2 Петрова      F3       112

Итак

Неформально можно заметить, что 1NF исключает повторяющиеся элементы или группы по горизонтали, а 2NF и 3NF исключают повторяющиеся элементы или группы по вертикали.

У опытных проектировщиков БД таблицы, не соответствующие нормальным формам, с первого взгляда вызывают ощущение "что-то-здесь-не-так" и взывают о немедленном вмешательстве. А схема данных, отвечающая 3-ей нормальной форме, представляется гармоничной и естественной. На практике, применяя здравый смысл при проектировании БД, мы с большой вероятностью получим схему данных, удовлетворяющую 3NF! Что, конечно, не отменяет значения формальных критериев нормализации (как способность логически рассуждать не отменяет значения формальной логики).