Нормализация данных — одно из самых важных понятий и концепций реляционной системы. Нормализованная система сводит к минимуму количество избыточных данных, при этом сохраняя их целостность. Нормализованной можно назвать базу данных, в которой все таблицы следуют правилам нормальных форм. Нормальная форма — набор правил, которые показывают, как надо организовать данные, что бы они были нормализованными (логично, да?). С момента основания реляционных систем появилось множество нормальных форм, но важно понимать, что следование каждой новой форме повышает нагрузку на систему (в чем вы скоро убедитесь). Поэтому, в более сложных системах всегда нужно уметь найти компромисс между степенью нормализации и производительности. В большинстве же случаев, вполне хватает следования первым трем нормальным формам, которые мы сейчас и рассмотрим.
Первая и главная нормальная форма требует от таблицы (а точнее, от ее проектировщика) следования следующим правилам:
- Каждый столбец в строке должен быть атомарным, т.е. столбец может содержать одно и только одно значение для заданной строки.
- Каждая строка в таблице обязана содержать одинаковое количество столбцов. Учитывая обязательную атомарность столбцов, следует, что все строки в таблице должны иметь одинаковое количество значений.
- Все строки в таблице, в общем, должны быть уникальны. Значения в столбцах могут дублироваться, но строки, взятые целиком — не могут.
Собственно, рассмотрим пример другой таблицы с фильмами:
title :varchar(255) | release :datetime |
| Назад в будущее — 1 | 1985.07.03, 1985.08.15 |
| Назад в будущее — 2 | 1989.11.22 |
| Назад в будущее — 2 | 1989.11.22 |
| Назад в будущее — 3 | 1991.05.25 |
Здесь сразу видны два нарушения первой нормальной формы. В первой строке, в столбце release указаны два значения (для США и Австралии), что запрещено первым правилом. Исправить это можно, записав еще одну строку для фильма, но с другим значением release.
| Назад в будущее — 1 | 1985.07.03 |
| Назад в будущее — 1 | 1985.08.15 |
Но это так же не лучшее проектное решение и мы сделаем по другому, после рассмотрения второй ошибки.
Как видите, в таблице дублируется вторая и третья строка, что запрещено уже третьим правилом. Для подобных таблиц удобно, в такой ситуации, добавить идентифицирующее поле, которое мы назовем id (кто-то предпочитает использовать полное название, т.е. filmId, но я сторонник первого варианта). Также это решит проблему фильмов с одинаковыми названиями, которые вышли в один и тот же день (хоть это и нереально для фильмов, с другими объектами такое вполне может произойти). Идентифицирующее поле делаем первичным ключом.
На заметку:
Первичный ключ — один или несколько столбцов, уникально идентифицирующих строку в таблице. Значения в столбце, объявленном как первичный ключ, не может дублироваться в нескольких строках. В свою очередь, если первичным ключом является связка из нескольких столбцов, запрет на дублирование действует на данные, взятые из столбцов в целом. В записи, первичный ключ обозначаем символами «PK», через дефис после типа (если я не прав, поправьте пожалуйста, но я всегда пользовался таким способом).
Пример, таблица с информацией о браках:
| husband :varchar(255)-PK | wife :varchar(255)-PK |
| Alex | Anna |
| Alex | Elizabeth |
| Alex | Anna |
Таблица Marriages.Как видите, здесь первичным ключом идут два поля таблицы — муж и жена, что бы исключить двойной выход замуж (будем считать, что у всех людей разные имена, т.е. Anna и Anna одна и та же женщина :-). Третьа строка в таблице ошибочна, так как связка таких значений уже есть в таблице.
После добавления идентифицирующего поля в таблице Films, создадим таблицу Releases, в которой будут хранится даты релизов фильмов в разных странах:
| id :int-PK | title :varchar(255) |
1 | Назад в будущее — 1 |
Таблица Films.| filmId :int-FK1 | date :date | country :varchar(255) |
1 | 1985.07.03 | USA |
1 | 1985.08.15 | Australia |
Таблица Releases.Вместо «PK», столбцы у нас теперь объявлены как «FK», что означает внешний ключ.
На заметку:
Внешний ключ — один или более столбцов в таблице, значения которых соответствуют значениям некоторых столбцов в другой таблице (как правило, ее первичным ключам). Внешние ключи нужно стараться использовать везде и всегда, когда между двумя таблицами существует взаимосвязь. Технически, современные системы поддерживают автоматический контроль ссылочной целостности при использовании внешних ключей (но более подробно, увы, не могу рассказать).
Так как у нас имеется прямая связь между таблицами Films и Releases, а точнее — отношение один ко многим (об отношениях поговорим позже), столбец filmId объявлен внешним ключом, так как он всегда соответствует полю id в одной из строк таблицы Films. Но здесь можно применить и более удобное решение. Например, в один и тот же день в одной и той же стране выходят разные фильмы. Для этого, у нас в таблице Films будут два фильма, а в таблице Releases две даты релиза. В таблице Releases будут практически одни и те же данные, за исключением поля filmId. Так почему бы не использовать одну строку в Releases для двух фильмов, если данные, по сути, одни и те же?
Для реализации этой задачи нам понадобится создать еще одну, связующую таблицу и назовем мы ее… Скажем, FilmReleases. Также, придется модифицировать таблицу Releases, добавив в нее идентифицирующий столбец id и убрав поле filmId (т.к. теперь дата релиза может быть одна для многих фильмов). Выглядеть все это будет так:
| id :int-PK | title :varchar(255) |
1 | Назад в будущее — 1 |
2 | Назад в будущее — 1 (Пиратка в России) |
Таблица Films.| id :int-PK1 | date :date | country :varchar(255) |
1 | 1985.07.03 | USA |
Таблица Releases.| filmId :int-FK1 | releaseId int:-FK2 |
1 | 1 |
2 | 1 |
Таблица FilmReleases.Теперь, благодаря связующей таблице, понятно, что у двух фильмов дата релиза в США одна и та же.
Итак, мы переходим ко второй нормальной форме.
Два правила второй нормальной формы говорят о том, что:
- Таблица обязана соответствовать первой нормальной форме.
- Все столбцы, не входящие в полный первичный ключ, должны зависеть от полного первичного ключа
Думаю, в понимании первого правила у вас проблем не возникло, так что рассмотрим второе. Итак, пример — таблица представления станций метро с информацией об архитекторах:
| archName :varchar(255) | archSurname: varcahr(255)-PK | title: varchar(255)-PK | completedDate :date |
| Александр | Андреев | Площадь Ленина | 1958.06.01 |
| Александр | Жук | Достоевская | 1991.12.30 |
Таблица Stations.В таблице первичным ключем объявлены поля archSurname (фамилия архитектора) и title (название станции). Если таблица следует нормальной форме, то все поля, не входящие в первичный ключ, зависят от него. Итак, смотрим: archName (имя архитектора) зависит от archSurname, и это логично. Поле completedDate (дата сдачи станции в эксплуатацию) зависит от title (от ее названия), это тоже логично. Но имя архитектора совершенно не зависит от названия станции, а дата сдачи станции, в свою очередь, никак не зависит от имени архитектора. Следовательно, два поля таблицы не зависят от соответственных полей в первичном ключе и таблица не соответствует второй нормальной форме.
Как мы решим проблему? Достаточно грамотный выход — раскидать данные по двум таблицам: Stations и Architectors. При этом нам понадобится третья, связующая таблица (что, плюс ко всему, позволит присваивать один проект нескольким архитекторам). В результате у нас получаются три таблицы:
| id: int-PK | title completed | Date :date |
1 | Площадь Ленина | 1958.06.01 |
2 | Достоевская | 1991.12.30 |
Таблица Stations.| id :int-PK | name :varchar(255) | surname: varcahr(255)-PK |
1 | Александр | Жук |
2 | Александр | Андреев |
Таблица Architectors.| stationId :int-FK1 | archId :int-FK2 |
1 | 2 |
2 | 1 |
Таблица StationArchitectors.Теперь у нас нет независимых от первичного ключа полей, таблица соответствует второй нормальной форме. Переходим к (догадались?) третьей нормальной форме.
Третья норма данных расширяет две предыдущие, неся в себе два правила:
- Таблица должна соответствовать второй нормальной форме.
- Все столбцы, не входящие в полный первичный ключ, должны зависеть от него и не должны зависеть друг от друга.
Здесь мне в голову приходит пример с таблицой автомобилей:
| id :int-PK | manufacturer :varchar(255) | shortBrand :varchar(20) | mileage :int |
1 | General Motors | GMC | 150000 |
2 | Bayerische Motoren Werke AG | BMW | 130000 |
Таблица Cars.У нас есть поля: id — идентифицирующее поле, manufacturer — концерн-производитель автомобиля, shortBrand — короткое название производителя и mileage — пробег. Так как id — первичный ключ и идентифицирующее поле, все остальные поля должны зависеть от него, что они и делают. Поле mileage не зависит ни от марки, ни от ее короткого бренда, поэтому пробег вообще не при делах. Дальше, следуя правилу, поля manufacturer и shortBrand не должны зависеть друг от друга, но они зависят. Если в столбце manufacturer будет записано «Волжский автомобильный завод», то в поле shortBrand необходимо будет записать «ВАЗ», что означает зависимость этих двух столбцов друг от друга.
Из такой ситуации выйти несложно, создадим таблицу Manufacturers и модифицируем таблицу Cars:
| id :int-PK | title :varchar(255)-PK | shortBrand :varchar(20) |
1 | General Motors | GMC |
2 | Bayerische Motoren Werke AG | BMW |
3 | Волжский автомобильный завод | ВАЗ |
Таблица Manufacturers.| id :int-PK | manufacturerId :int-FK1 | mileage :int |
1 | 1 | 150000 |
2 | 2 | 130000 |
Таблица Cars.Теперь таблицы соответствуют третьей нормальной форме, с чем мы ее и поздравляем.
Upd: Умные хабрапроектировщики советуют не приводить таблицы, столбцы которых являются синонимами (имеется в виду таблица Manufacturers) к третьей нормальной форме, т.к., по сути, приходится все синонимические поля включать в первичный ключ (что идет против требований к его минимизации).
Итак, мы рассмотрели один из немаловажных этапов проектирования базы данных. Умение нормализировать данные всегда поможет вам проектировать целостную и более ли менее чистую БД, не говоря о том, что это просто хороший тон.
Оставайтесь нормализованными и до встречи в следующей статье.
PS (ВУ):
Существует еще 4НФ и 5НФ, а также промежуточная между 3НФ и 4НФ - нормальная форма Бойса—Кодда (NFBC/НФБК).
PPS (ВУ):
Но не все так гладко и пушисто. Зачастую, чтобы извлечь информацию из нормализованной базы данных, приходится конструировать очень сложные запросы, которые, главным образом, из-за большого количества соединений таблиц, работают очень медленно.