Проектирование

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

Функциональные требования есть ни что иное, как SQL-запросы к базе данных. Обращаем ваше внимание на то, что данное определение цели проектирования принципиально отличается от распространенного в классической литературе. Так, например, К. Дейт в книге "Введение в системы баз данных" формулирует задачу проектирования базы данных, как определение таблиц, столбцов и отношений независимо от функциональных требований, а лишь на основе самих данных. "В общем проблема формулируется следующим образом: как в некоторой базе данных для заданного набора данных выбрать подходящую логическую структуру?" - пишет К.Дейт. При этом он не дает определения, а что же такое "подходящая логическая структура". Как вы помните, в главе "Язык SQL" было сказано, что основное назначение базы данных - хранение данных, многопользовательский доступ к ним и операции над ними. Практика показывает, что проектировать базу данных без учета операций над ними НЕВОЗМОЖНО. Нет ничего универсального. Чем шире универсальность, тем уже применимость системы. Поясним сказанное на простом примере. Пусть у нас есть данные о курсах, преподавателях, читающих курсы и учебниках по данным курсам.

Курс Преподаватель Учебник
Физика Иванов Бутиков
Физика Иванов Савченко
Физика Петров Бутиков
Физика Петров Савченко
Физика Сидоров Бутиков
Математика Иванов Яковлев
Математика Иванов Сканави
Математика Петров Яковлев
В зависимости от функциональных требований к системе каждый столбец данной таблицы может быть либо сущностью, либо атрибутом сущности, поэтому мы получаем разные структуры такой базы данных. Допустим, если мы автоматизируем работу библиотеки, то сущностью будут учебники, а данные о преподавателях и курсах пойдут, как атрибуты. В такой системе, в большинстве запросов нас будут интересовать учебники и предметы, а преподаватели просто, как сопутствующая информация. Напротив, если же мы создаем систему для отдела кадров, то нас будут интересовать в первую очередь преподаватели, во вторую читаемые ими курсы, и в самую последнюю очередь, отдел кадров интересуется учебниками, которые нужны преподавателю. Данный пример, конечно, очень упрощен, но тем не менее он отражает суть вопроса.

Итак, перейдем к сути вопроса. Опять же таки, в абсолютном большинстве книжек про базы данных написано про нормализацию... Эту нормализацию давно никто не применяет и доподлинно неизвестно, а была ли эта нормализация, или же она существовала в воображении теоретиков. Мы не будем здесь разбирать абстрактные примеры и бороться с ветряными мельницами! За несколько лет работы и проектирования баз данных автору и многим его коллегам не приходилось действовать так, как учит теория и как написано в любой дурацкой книге технического пИсателя, не способного ни на что, кроме тупого переписывания справочников и других книжек.
Во-первых, стоит сделать важное замечание. Процесс проектирования - это итерационный процесс. Среди этапов проектирования можно выделить следующие:

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

В простых системах можно уложиться в 1-2 итерации. Рассмотрим пример проектирования базы данных для системы конференций. Конференция в реальном мире представляет собой съезд специалистов, каждый из которых делает доклад(ы). После доклада у слушателей имеется возможность задать вопросы. Конференция обычно делится на несколько тематических секций. Такого рода информационная система должна иметь три уровня доступа:

Пользовательский.
Автора статей.
Администратора конференции.

Функциональные требования к пользовательской части веб-сервера:

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

Функциональные требования к уровню доступа авторов статей:

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

Функциональные требования к администраторскому доступу:

Разрешение регистрации автора.
Разрешение публикации статьи.
Рассылка информационного сообщения по всем электронным адресам пользователей системы.

Очевидным образом напрашиваются следующие сущности: конференция, секция, доклад, вопрос, специалист (автор доклада). Также довольно очевидно, что в конференции много секций, т.е. отношение один ко многим, в секции много докладов, на доклад много отзывов. Пока не совсем понятно, как соотносятся с этими таблицами администраторы, авторы и слушатели. Для начала давайте заполним эти таблицы атрибутами. В таблице конференций (conf) будет первичный ключ conf_id и название (name). Аналогичным образом и для секции (sec), sec_id, name. В таблице секции будет еще присутствовать внешний ключ, который будет ссылаться на первичный ключ conf_id таблицы конференций для осуществления связи один ко многим. В обе эти таблицы также можно включить поле описание (description), но это не обязательно. Далее идет таблица докладов article. В таблице докладов у нас первичный ключ - article_id, внешний ключ sec_id, который ссылается на первичный ключ таблицы секций, название доклада - name, ключевые слова - keywords, аннотация доклада - description, page - номер страницы в печатном сборнике и it_date - дата публикации. Теперь переходим к таблице отзывов на доклады - response. В этой таблице будет первичный ключ response_id, внешний ключ article_id, тема отзыва subj, текст отзыва it_text, иконка с улыбающейся рожицей smile и дата отзыва it_date. В таблице отзывов должны быть сведения об авторе отзыва. Но тут возникает вопрос. Казалось бы, можно просто добавить внешний ключ, ссылающийся на таблицу авторов и проблема решена. Но не так все просто. Если вы так поступите, то вы обяжете всех регистрироваться в вашей системе. С точки зрения базы данных это хорошее решение, т.к. в этом случае внешний ключ не будет содержать NULL-значений и будет обеспечиваться целостность базы данных. Но вот с точки зрения пользователя веб-сайта это плохо. Пользователь думает: "Ребята, да я вас не знаю, с чего мне тут регистрироваться, как вы будете использовать мои данные, да я вообще зашел сюда один раз статью сына прочитать и т.д.." А что делать, если пользователь хочет при подаче отзыва на статью указать адрес электронной почты отличный от того, что он указал при регистрации? Многие пользователи, опасаясь получения всевозможной незапрашиваемой корреспонденции - рекламы и т.п., имеют несколько почтовых ящиков. Адрес своего главного почтового ящика они дают только друзьям и для деловых контактов и стараются его особо нигде не светить. Для всех остальных случаев заводится ящик на бесплатном сервере, например, mail.ru или chat.ru. Итак, возвращаясь к нашим баранам, решение со внешним ключом не очень хорошее. Значит нам надо включить в таблицу response еще поля: имя автора, адрес электронной почты автора и адрес его веб-узла. Такое решение имеет тоже небольшой недостаток - избыточность данных. Если у пользователя поменяется адрес электронной почты, то нет никакой возможности изменить этот адрес в таблице отзывов. В общем-то это проблема не страшная, т.е. ничего катастрофического в том, что автор отзыва не получит сообщения по электронной почте о том, что появился еще один отзыв по теме данного доклада, нет. Можно сказать, что спасение утопающего есть дело рук самого утопающего. Мы пойдем именно по этому пути. Но если вам уж так захочется все-таки решить эту проблему, то в таблицу отзывов надо включить и внешний ключ и три атрибута об авторе отзыва. Но в этом случае нужно контролировать условие, что либо внешний ключ содержит NULL-значение, либо поля name, email & http содержат NULL-значения, иначе получится противоречие. Осталось решить, что же делать с администраторами, авторами докладов и слушателями авторов отзывов на доклады. Поскольку все они люди, но с разным статусом, то будем хранить о них данные в одной таблице. Назовем ее таблица авторов - authors. В этой таблице будет первичный ключ author_id, имя автора - name, пароль pwd, дата регистрации в системе - it_date, адрес электронной почты - email и права доступа - state. Поскольку у одного автора может много докладов, а у одного доклада много авторов, то они относятся, как многие ко многим. Итак, получаем: