Руководство по проектированию и разработке
Мы регулярно публикуем материалы о практическом использовании инструментов обработки информации и очень редко пишем о теоретических аспектах создания баз данных, хотя именно этот элемент предшествует написанию кода, проектированию форм... Нормализация данных - один из наиболее часто встречающихся элементов БД, и в этой небольшой статье мы хотим предложить вашему вниманию краткое руководство по проектированию и разработке баз данных.
Давайте сначала сформулируем основные правила, которым нужно следовать при проектировании базы данных, а затем более подробно рассмотрим каждое из правил в отдельности.
Исключайте повторяющиеся группы - для каждого набора связанных атрибутов создайте отдельную таблицу и снабдите ее первичным ключом.
Исключайте избыточные данные - если атрибут зависит только от части составного ключа, переместите атрибут в отдельную таблицу.
Исключите столбцы, которые не зависят от ключа - если атрибуты не вносят свою лепту в описание ключа, переместите их в отдельную таблицу.
Изолируйте независимые множественные отношения - никакая таблица не может содержать два или более отношений 1:n или n:m, которые не имеют непосредственной связи.
Изолируйте семантически связанные множественные отношения - могут быть практические ограничения на информацию, которые оправдывают разделение логически связанных отношений многие-ко- многим.
Исключайте повторяющиеся группы
В исходном наборе данных за описанием каждого щенка идет список трюков, которым щенок обучен. Некоторые знают десяток трюков, другие ни одного. Для того чтобы ответить на вопрос: "Может ли Фифи кувыркаться", нам сначала нужно найти запись, в которой описана Фифи, а затем просканировать список трюков в этой записи. Это неудобно, неэффективно и исключительно некрасиво. Перемещение списка трюков в отдельную таблицу (TRICK) значительно поможет делу. Отделение повторяющихся списков трюков от информации о самих щенках приводит нас к первой нормальной форме. Номер щенка в таблице трюков должен соответствовать значению первичного ключа в таблице щенков и является вторичным (foreign) ключом, по которому две таблицы связываются друг с другом. Теперь мы можем получить ответ посредством единственного обращения к данным: нам нужно выяснить, есть ли в таблице трюков запись, в которой имеются идентификатор Фифи и идентификатор "кувыркаться".
В таблице TRICKS первичный ключ сформирован из номера щенка и идентификатора трюка. Это имеет смысл для атрибутов "Где научился" и "Уровень опыта", так как они уникальны для каждой комбинации щенок/трюк. Но при этом название трюка зависит только от идентификатора этого трюка. В результате одно и то же название трюка появляется многократно при добавлении в таблицу соответствующего идентификатора.
Предположим, мы хотим изменить классификацию трюка, дать ему новый идентификатор. Изменение должно быть выполнено для каждого щенка, который владеет этим трюком! Если этого не сделать, у вас окажется несколько щенков, с которыми связан один и тот же трюк только под разными идентификаторами. Это аномалия модификации.
Или, предположим, последний щенок, который знает некоторый трюк, более не выступает. Его записи необходимо удалить из базы данных. И трюк больше не присутствует в базе. Это аномалия удаления. Для исключения такого рода проблем нам нужна вторая нормальная форма.
Для достижения этого нам необходимо отделить атрибуты, зависящие от обеих частей ключа, от тех, которые зависят только от идентификатора трюка. В результате мы получаем две таблицы: TRICKS, в которой хранятся названия трюков для каждого идентификатора, и PUPPY- TRICKS, в которой хранится список трюков, которым обучен каждый щенок.
Теперь мы можем изменить классификацию трюка за одну операцию: находим идентификатор в таблице TRICKS и меняем его название. Результат становится доступен немедленно.
Исключите столбцы, которые не зависят от ключа
Таблица с данными о щенках, PUPPY, удовлетворяет требованиям первой нормальной формы - она не содержит повторяющихся групп. Она удовлетворяет второй нормальной форме, так как не имеет повторяющихся значений первичного ключа. Но ключ определяется полем, где хранится номер щенка, а поля "номер питомника" и "расположение питомника" описывают только питомник, а не щенка. Для удовлетворения требованиям третьей нормальной формы их необходимо переместить в отдельную таблицу. Так как в этой таблице будут хранится данные о питомнике, поле с кодом питомника определяет первичный ключ новой таблицы KENNELS.
Мотивация аналогична той, что мы приводили для второй нормальной формы. Мы хотим избежать аномалий удаления и модификации. В том случае, если в нашей базе данных нет упоминаний о щенках из Дейзи Хилл Паппи Фарм, то при использовании предыдущего дизайна, этого питомника в базе не будет вообще!
Изолируйте независимые множественные отношения
Это относится только к базам данных, в которых присутствуют отношения один-ко-многим и многие-ко-многим. Пример отношения один-ко-многим: один питомник может растить нескольких щенков. Пример отношения многие-ко-многим: один щенок может знать несколько трюков и несколько щенков могут знать один и тот же трюк. Предположим, мы хотим добавить в таблицу PUPPY-TRICKS атрибут "Костюм". Таким образом, мы сможем найти щенков, которые умеют "служить" и при этом одеты в определенную попонку. Четвертая нормальная форма протестует против такого подхода. Два атрибута не связаны осмысленным отношением. Щенок может уметь ходить на задних лапах и его периодически могут одевать в гидрокостюм. Однако, не обязательно, что он может это делать в одно и то же время. Как вы сможете представить эту ситуацию, если оба атрибута хранятся в одной таблице?
Изолируйте семантически связанные множественные отношения
Обычно связанные атрибуты размещаются в одной таблице. Например, если нам действительно необходимо фиксировать какие щенки обучены каким трюкам и в какой попонке они при этом выступают, нам нужно хранить атрибут Costume в таблице PUPPY-TRICKS. Но иногда определенные характеристики данных делают более эффективным разделение даже логически связанных атрибутов.
Представьте, что наша база данных хранит информацию о породах щенков в каждом питомнике и какой клуб поставляет щенков для этих питомников. Напрашивается организация таблицы Питомник-Клуб- Порода, удовлетворяющей требованиям четвертой нормальной формы. Если любой питомник может поставлять щенков любой породы от любого клуба, такая схема работает прекрасно.
Теперь предположим, что правительство приняло закон о запрещении эксклюзивных партнерств. Это означает, что питомник, продающий любую породу, обязан предлагать ее от всех клубов, с которыми он имеет дело. Другими словами, если питомник Khabul продает Афганских борзых и хочет продать щенков из Daisy Hill, он обязан продавать в том числе и афганских борзых Daisy Hill.
Необходимость в пятой нормальной форме становится ясной если мы рассмотрим вопросы добавления новых записей и удаления существующих. Предположим, питомник решает начать продажи трех новых пород: спаниэлей, догов и вест-индийских поедателей бананов. Предположим также, что питомник уже имеет дело с тремя клубами, которые поставляют эти породы. Описанное решение потребует в базе данных девяти новых записей: для каждой комбинации клуб/порода.
Разделение таблицы снижает число новых записей до шести.