Статья
Версия для печати
Обсудить на форуме
Проектирование баз данных. Нормализация.


Автор: Ueasley.
Публикуется с разрешения автора.

Оригинал статьи: http://habrahabr.ru/blogs/development/45707/
Под редакцией Клуба программистов «Весельчак У» (далее — «ВУ»).

Содержание.


Нормализация и проектирование.

Проектирование баз данных, как правило, играет одну из ключевых ролей в большинстве проектов. Грамотно спроектированная база позволяет без особых проблем вносить изменения, изменять структуру системы. Так как сейчас наиболее популярны реляционные БД, мы рассмотрим основы нормализации и проектирования применительно к реляционной модели. А конкретно, сегодня мы поговорим об очень важной составляющей процесса проектирования — о нормализации.

Реляционная модель.

Основоположником реляционных баз данных считается Эдгар Кодд, который в 1970 году опубликовал в одном журнале статью «Реляционная модель данных для больших банков данных совместного использования». В статье Эдгар рассказывал про новую систему управления данными, основанную на математических принципах теории множеств и теории предикатов. Такая модель поддерживала точность и непротиворечивость данных, а также удобные извлечение и модификацию данных, со структурой, не зависящей от приложений и платформ.
Основа любой реляционной модели — таблица, организованная как структура типа столбец-строка (по ходу статьи, я могу использовать слово «поле», вместо слова «столбец»). Таблица (не считая связывающих) является представлением какого-то объекта, который вы хотите хранить в БД. Например, ниже представлена довольно простая таблица Films, в которой, логично предположить, хранится информация о фильмах.

id :int-PK
title :varchar(255)
release :datetime
1
Назад в будущее — 11985.07.03
2
Назад в будущее — 21989.11.22
Таблица Films.

Как видите, таблица, по сути, является физическим представлением объекта «Фильм» с некоторыми его данными. К этой таблице мы вернемся чуть позже, а сейчас о нормализации.

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

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

Первая и главная нормальная форма требует от таблицы (а точнее, от ее проектировщика) следования следующим правилам:

  • Каждый столбец в строке должен быть атомарным, т.е. столбец может содержать одно и только одно значение для заданной строки.
  • Каждая строка в таблице обязана содержать одинаковое количество столбцов. Учитывая обязательную атомарность столбцов, следует, что все строки в таблице должны иметь одинаковое количество значений.
  • Все строки в таблице, в общем, должны быть уникальны. Значения в столбцах могут дублироваться, но строки, взятые целиком — не могут.

Собственно, рассмотрим пример другой таблицы с фильмами:

title :varchar(255)
release :datetime
Назад в будущее — 11985.07.03, 1985.08.15
Назад в будущее — 21989.11.22
Назад в будущее — 21989.11.22
Назад в будущее — 31991.05.25

Здесь сразу видны два нарушения первой нормальной формы. В первой строке, в столбце release указаны два значения (для США и Австралии), что запрещено первым правилом. Исправить это можно, записав еще одну строку для фильма, но с другим значением release.

Назад в будущее — 11985.07.03
Назад в будущее — 11985.08.15

Но это так же не лучшее проектное решение и мы сделаем по другому, после рассмотрения второй ошибки.
Как видите, в таблице дублируется вторая и третья строка, что запрещено уже третьим правилом. Для подобных таблиц удобно, в такой ситуации, добавить идентифицирующее поле, которое мы назовем id (кто-то предпочитает использовать полное название, т.е. filmId, но я сторонник первого варианта). Также это решит проблему фильмов с одинаковыми названиями, которые вышли в один и тот же день (хоть это и нереально для фильмов, с другими объектами такое вполне может произойти). Идентифицирующее поле делаем первичным ключом.

На заметку:
Первичный ключ — один или несколько столбцов, уникально идентифицирующих строку в таблице. Значения в столбце, объявленном как первичный ключ, не может дублироваться в нескольких строках. В свою очередь, если первичным ключом является связка из нескольких столбцов, запрет на дублирование действует на данные, взятые из столбцов в целом. В записи, первичный ключ обозначаем символами «PK», через дефис после типа (если я не прав, поправьте пожалуйста, но я всегда пользовался таким способом).
Пример, таблица с информацией о браках:

husband :varchar(255)-PKwife :varchar(255)-PK
AlexAnna
AlexElizabeth
AlexAnna
Таблица 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.03USA
1
1985.08.15Australia
Таблица 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.03USA
Таблица Releases.

filmId :int-FK1releaseId int:-FK2
1
1
2
1
Таблица FilmReleases.

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

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

Два правила второй нормальной формы говорят о том, что:

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

Думаю, в понимании первого правила у вас проблем не возникло, так что рассмотрим второе. Итак, пример — таблица представления станций метро с информацией об архитекторах:

archName :varchar(255)archSurname: varcahr(255)-PKtitle: varchar(255)-PKcompletedDate :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-PKname :varchar(255)surname: varcahr(255)-PK
1
АлександрЖук
2
АлександрАндреев
Таблица Architectors.

stationId :int-FK1archId :int-FK2
1
2
2
1
Таблица StationArchitectors.

Теперь у нас нет независимых от первичного ключа полей, таблица соответствует второй нормальной форме. Переходим к (догадались?) третьей нормальной форме.

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

Третья норма данных расширяет две предыдущие, неся в себе два правила:

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

Здесь мне в голову приходит пример с таблицой автомобилей:

id :int-PKmanufacturer :varchar(255)shortBrand :varchar(20)mileage :int
1
General Motors GMC
150000
2
Bayerische Motoren Werke AGBMW
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 MotorsGMC
2
Bayerische Motoren Werke AGBMW
3
Волжский автомобильный заводВАЗ
Таблица Manufacturers.

id :int-PKmanufacturerId :int-FK1
mileage :int
1
1
150000
2
2
130000
Таблица Cars.

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

PS (ВУ):
Существует еще 4НФ и 5НФ, а также промежуточная между 3НФ и 4НФ - нормальная форма Бойса—Кодда (NFBC/НФБК).

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

Ссылки.


Версия для печати
Обсудить на форуме