Проектирование базы данных

Сегодня большенство веб-приложений любой сложности используют базы данных. Простенький блог, высоконагруженная социальная сеть или электронный магазин должны иметь хранилище данных(исключением может быть случай, когда в качестве источника данных ваш ресурс использует сторонние сервисы других компаний, которые предоставляют вам доступ к своей информации через определенный API). Это может быть информация о товарах, услугах, зарегистрированных пользователях, написаных статьях и т. д. Причем хранилище данных должно быть соответствующим образом структурировано, чтобы можно было однозначно связать информацию об объектах в системе. Существует множество решений данной проблемы, и скорее всего единого рецепта на все случаи жизни нет. В данной статье я хочу затронуть тему проектирования реляционной базы данных. Здесь я не буду приводить такие понятия, как нормализация модели данных и формы нормализации, а также объяснять основные положения и понятия реляционных баз данных. Это - более теоретический аспект, чем практический. Также я не буду упоминать конкретные СУБД(система управления базами данных) и приводить какие-либо SQL-запросы(Structured Query Language) для построение схемы базы данных. Это - просто инструменты.

В данной статье я хочу поделиться подходом к проектированию базы данных на небольшом, но достаточно понятном примере, который не заставит вас особо задумываться над предметной областью. Итак, давайте перейдем к постановке задачи.

Постановка задачи и суть проблемы

Разрабатывается социальная сеть. Я намеренно не буду указывать тематику сайта, так как подобные системы имеют очень много общего. И часть этого "общего" мы возьмем за основу. Это будет небольшой набор возможностей, которые характерны для большенства социальных сетей.

Итак, вот список этих функций:

- пользователи могут зарегистрироваться в системе и иметь свой профиль - пользователь может вести свой блог и публиковать статьи

- пользователь может иметь свои фотоальбомы(например, как в контакте или на мэйл ру)

- пользователь может выложить свои фотографии на сайт(для простоты будем иметь ввиду, что фотография может находиться только в каком-либо альбоме)

- у пользователя могут быть друзья - это другие пользователи(типичная возможность социальных сетей)

- пользователь может оставлять комментарии к фотографии, фотоальбому, статье при условии, что он(пользователь) авторизован в системе

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

- при создании/редактировании контента(фотографии, альбома или статьи) пользователь имеет возможность редактировать теги(метки) данного контента(очень распространенная функциональность среди сайтов в стиле Web 2. 0).

Вот очень малая часть того, что необходимо социальной сети. Я специально упростил задачу, так как наша цель - это понимание сути и подхода к проектированию базы данных. Этих требований будет вполне достаточно для нас. Итак, техзадание у нас на руках. Двигаемся дальше.

Выявление объектов

Когда требования приложения более или менее понятны, можно переходить к выявлению сущностей, которые фигурируют в системе. Они обычно в тексте являются существительными. Давайте попробуем их выделить. Итак, в техзадании сказано, что в системе присутствуют пользователи. Значит одной из сущностью приложения является пользователь. Можно его выделить. Далее сказано, что у пользователя есть свой профиль. Не думаю, что следует в данном случае профиль выделять как сущность. Она никак не влияет на логическую модель данных. Профиль - это просто раздел сайта и не более того.

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

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

Пользователь может заводить друзей на сайте. Здесь новых сущностей мы не находим, так как друзья - это те же пользователи. Скорее здесь сообщается, что между различными юзерами может устанавливаться связь "дружба". Далее нужно выделить сущность комментария, метки(тега для контента) и голоса(за контент), рассуждая аналогичным образом. И в итоге мы получаем следующий набор сущностей, указанных на рис. 1.

Рис. 1 Выявленные сущности системы

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

Модель "сущность-связь"

Для начала я представлю саму диаграмму "сущность-связь" для нашего примера.

Рис. 2 Диаграмма "сущность-связь"

Сплошная линия показывает логическую связь(ассоциацию) между сущностями модели данных. Около конца линии(в UML его называют полюсом) указывается кратность. Кратность - это количество экземпляров одной сущности, которые могут быть ассоциированы с одним экземпляром другой сущности через одну связь(связей между двумя рассматриваемыми сущностями может быть больше одной в общем случае, но они будут иметь разный смысл).

Рассмотрим, например, ассоциацию между PhotoAlbum и Photo. В данном случае "1" показывает, что конкретная взятая фотография может быть связана только с одим фотоальбомом. С другой стороны символ "*"(читается как "много") на противоположном конце связи говорит о том, что конкретный фотоальбом может быть связан с 0 или более фотографиями. Такой тип связи между сущностями называют 1:N(один ко многим).

Давайте проанализируем диаграмму, изображенную на рис. 2.

Если пользователи могут заводить друзей, то между этими сущностями должна быть связь. Если проанализировать ситуацию, то будет ясно, что это - связь типа N:M(многие ко многим). Т. е. любой пользователь может иметь множество друзей.

Очевидно, что если на сайте существует фотоальбом, то он должен иметь своего владельца(пользователя) и только единственного. С другой стороны пользователь может не иметь фотоальбомов или иметь их в неопределенном количестве. В данном случае связь "фотоальбом-пользователь" имеет тип 1:N(один ко многим).

Аналогичная ситуация в случае ассоциаций "пользователь-статья", "фотоальбом-комментарий", "фотоальбом-голос", "фотография-комментарий", "фотография-голос", "пользователь-комментарий" и "пользователь-голос"(эти связи типа 1:N). В техзадании сказано, что к фотоальбомам могут подписываться теги. Например, при создании нового или редактирования старого альбома пользователь может редакровать его метки(из этих меток обычно формируется облако тегов). Т. е. экземпляр сущности альбома может быть ассоциирован с ноль или более тегами. С другой стороны один и тот же тег может быть связан с неопределенным количеством альбомов. Следовательно ассоциация "фотоальбом-тег" имеет тип N:M(многие ко многим). Аналогичная ситуация в случае связей "фотография-тег" и "статья-тег".

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

Итак, мы получили диаграмму "сущность-связь", по которой необходимо создать таблицы базы данных и связи между ними. Для этого обычно сначала создается по одной таблице для каждой сущности. Далее необходимо определить столбцы таблиц. На самом деле на первых итерация реализации проекта мало понятно что нужно действительно для объектов в системе. Тем не менее это не мешает нам двигаться вперед. Выделите те свойства объектов, в которых на 100% уверены. Например, уникальный идентификатор встречается практически у всех объектов. Поэтому поле ID можно добавить к любой таблице. А остальные столбцы определятся по ходу реализации проекта(они ещё сто раз поменяются :)). После этого нужно определить связи между таблицами(при этом могут добавиться новые таблицы).

Я приведу свою версию схемы базы данных и поясню ее ниже.

Рис. 3 Схема базы данных

На рис. 3 таблицы обозначаются прямугольниками с закругленными углами, а связи между таблицами - сплошной или пунктирной линией.
Символ на конце связи соответствует символу "*", а символ соответствует символу "1" на диаграмме "сущность-связь".

Данная схема не является единственной возможной для реализации наших требований и не лишена недостатков.

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

Пользователи

Итак, одной из сущностью системы является пользователь(см. рис. 2). Можно смело создавать таблицу user(пользователь). В качестве полей данной таблицы можно определить id(идентификатор), nick(имя на сайте), email(электронная почта) и password(пароль на сайте). Эти столбцы вероятнее всего будут нужны. Здесь же необходимо определить первичный ключ для однозначной идентификации каждой строки в таблице. Поле id для этого и нужно. Первичный ключ налагает ограничение на столбец(или группу столбцов) таким образом, что каждая строка в таблице имеет в этом столбце уникальное значение(по отношению к другим строкам) и не содержит значения NULL. Опять повторюсь, что в этой статье я не буду приводить основные понятия и положения реляционных баз данных. Предполагается, что читатель уже имеет определенный теоретический запас. Далее нам необходимо сделать так, чтобы между пользователями можно было устанавливать связь "дружбы". Мы говорили о том, что данная ассоциация имеет тип N:M(многие ко многим). Для реализации связи данного типа обычно вводят дополнительную таблицу. Поля данной таблицы содержат идентификаторы сущностей, которые необходимо связать(и возможно еще какие-либо столбцы, если они характерезуют именно связь, а не рассматриваемые сущности). Поэтому мы создаем таблицу user_friend и создаем в ней два поля - userId и friendId. В этих полях будут храниться значения идентификаторов пользователей. Таким образом нужно вставить строку в данную таблицу с идентификаторами двух различных пользователей, чтобы их "подружить". Но это еще не всё. Необходимо для этой таблицы указать первичный ключ. В данном случае первичным ключом могут быть обе колонки - userId и friendId. Теперь уникальность записей обеспечена. После этого нам нужно гарантировать, что все записи в user_friend содержат идентификаторы только тех пользователей, которые действительно существуют на сайте. Для этого каждому столбцу данной таблицы необходимо поствить ограничение внешнего ключа, который будет ссылаться на колонку id из таблицы user. Внешний ключ - это один из типов ограничений, которые можно накладывать на столбцы таблиц реляционной базы данных. При создании внешнего ключа нужно указать колонку, на которую он(ключ) ссылается. Теперь пользователь не сможет "дружить" с несуществующим пользователем - СУБД не позволит.

Фотографии

Мы говорили о том, что пользователи могут выкладывать фотографии на сайт. Для этого мы выделили сущность photo(см. рис. 2). Создадим таблицу photo для хранения информации о фотографиях на сайте. Здесь же добавим поле id и поставим ему ограничение первичного ключа для однозначной идентификации всех фото на сайте. Добавим к данной таблице колонки title(название) и url(ссылка на файл фотографии в сети). Название фотографии практически всегда нужно, а ссылка на сам файл фотографии в сети тем более. Хочу сразу отметить, что указания url в качестве одной из колонок сделан для упрощения примера. В реальной жизни полный урл ресурса хранить в таблице не целесообразно, так как файлы могут "переезжать" с сервера на сервер и сама структура хранилища может меняться. Однако фотографии на сайте не могут быть без фотоальбома. Поэтому создадим таблицу photoAlbum, в которой так же, как и в photo, добавим поле id и установим ему ограничение первичного ключа. Обычно альбомы на сайте именуются в виде строки. Поэтому можно смело добавлять в photoAlbum колонку title.

Нам известно, что связь "фотоальбом-фотография" имеет тип 1:N. Для реализации данной ассоциации добавим в таблицу photo столбец и назовем его photoAlbumId. Эта колонка будет содержать идентификатор фотоальбома, к которому рассматриваемая фотография принадлежит. Таким образом для того, чтобы "положить" фотографию в какой-нибудь альбом, нужно вставить новую(или модифицировать существующую) строку в таблице photo, указав в значении колонки photoAlbumId идентификатор нужного альбома. Так же необходимо указать, что поле photoAlbumId не должен содержать значений NULL. Если альбом существует на сайте, то он должен принадлежать одному единственному пользователю. Мы выяcнили, что связь "пользователь-фотоальбом" типа 1:N. Значит реализуется она аналогично "фотоальбом-фотография". Добавим в таблицу photoAlbum поле ownerId, которое имеет смысл идентификатора пользователя, к которому данный фотоальбом принадлежит. Установим ограничение внешнего ключа на ownerId так, чтобы он ссылался на поле id таблицы user. Также необходимо указать, что ownerId не может содержать значения NULL.

Итак, мы построили часть схемы базы данных. Теперь у нас есть пользователи, которые могут заводить друзей и выкладывать фотографии на сайт, группируя их по альбомам. Круто. Идем дальше.

Голосования

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

Для того, что бы отслеживать голосования за фотографии, создадим таблицу photo_vote, полями которой являются userId(идентификатор голосующего пользователя), photoId(идентификатор фотографии, за которую голосует пользователь) и value(булевая величина, которая может иметь только два значения - 0 или 1). Таким образом, данная таблица хранит информацию о том, какие фотографии нравятся или не нравятся определенным пользователям. Далее нужно установить ограничение первичного ключа, чтобы обеспечить уникальность записей в таблице. Мы понимаем, что одна взятая фотография не может нравится и не нравится определенному пользователю одновременно. Учитывая то, что отдельный взятый пользователь может голосовать за неопределенное количество фотографий и у одной фотографии может быть множество голосов различных пользователей, нужно установить группу полей userId и photoId в качестве первичного ключа. Для того, чтобы гарантировать голосование только от лиц пользователей, существующих на сайте, установим ограничение внешнего ключа на поле userId таблицы photo_vote так, чтобы он(ключ) ссылался на столбец id таблицы user. Это необходимо для реализации ссылочной целостности в базе данных. Теперь СУБД не позволит вписывать в ячейки столбца userId таблицы photo_vote значения идентификаторов пользователей, которых нет в таблице user.

Рассуждая аналогично, можно понять, что для поля photoId таблицы photo_vote нужно задать ограничение внешнего ключа так, чтобы он(ключ) ссылался на поле id таблицы photo.

Интересно заметить, что к этой же структуре можно прийти, рассматривая голосование за фотографии как связь между пользователем и фотографией. Причем значение самого голоса(нравится или нет) является свойством связи и не принадлежит ни фотографии, ни пользователю. Данная ассоциация имеет тип N:M. Опираясь на сказанное выше, можно понять, что аналогичным образом строятся голосования за фотоальбом и статью.

Это не единственное решение. Например, можно было бы объединить все голосования в одну таблицу vote с полями userId, contentId, contentType и value. Здесь contentId - идентификатор фотографии или фотоальбома, или статьи. Колонка contentType содержит строку, которая указывает на тип контента(например, "photo", "album" или "article"). Подобное решение уменьшает число таблиц в базе данных и не увиличивает его по мере прибавления новых типов контента(например, может добавиться музыкальная композиция или видеоролик). На этом, пожалуй, заканчиваются приемущества данного подхода. Теперь о недостатках.

Во-первых, организовать ссылочную целостность на уровне СУБД будет сложнее. Наример, можно будет для таблицы vote написать триггер, который при модификации данных будет проверять наличие соответствующего контента в базе по значению в поле contentType и идентификатору contentId. Это - дополнительный код, который нужно будет сопровождать. При этом триггеры увеличивают время модификации данных. Во-вторых, vote становится сложнее в сопровождении. Программировать доступ к данной таблице становится уже не так очевидно. Нужно будет дополнительно заключать соглашение, что для фотографии поле contentType = "photo", а не "Photo" или "_photo". Аналогично и для других типов контента.

В-третьих, число строк в таблице vote будет быстро рости. Следовательно и нагрузка на СУБД будет выше при обращении к vote. В-четвертых, в общем случае тип поля идентификатора у различных сущностей может не совпадать. Например, в photo столбец id может быть целочисленным, а в article - типа varchar(символьная строка переменной длины).

Комментирование

При составлении диаграммы "сущность-связь"(рис. 2) мы выделили комментарий в качестве одного из типов объектов в системе. Можно смело создавать таблицу comment с колонками id(установив на нее ограничение первичного ключа), text(для хранения содержимого комментария) и date(время, создания комментария). Нам известно, что пользователь имеет связь с комментарием типа 1:N. Поэтому в таблице comment мы создаем колонку c именем authorId (идентификатор пользователя, который написал данный комментарий)и устанавливаем ей ограничение внешнего ключа так, чтобы он ссылался на поле id таблицы user.

Теперь нужно обеспечить связь комментария с фотографией(для других типов комментируемых объектов техника построения связей аналогична). Для этого создадим новую таблицу с именем photo_comment и добавим в нее две колонки - commentId и photoId. Хочу заметить, что мы вводим новую таблицу(photo_comment) несмотря на то, что тип связи 1:N. Дело в том, что комментировать мы можем не только фотографии, но и фотоальбомы, и написанные статьи. В данном случае обеспечить связь между комментарием, фотографией, фотоальбомом и статьей не получится простым добавлением внешнего ключа в таблицу comment. Возникнет неоднозначность в определении типа комментируемого объекта. Нужно ещё учитывать тот факт, что типы идентификаторов всех сущностей могут быть разными. Например, у фотографии id может быть целочисленным, а у фотоальбома - типа GUID(Globally Unique Identifier). Чтобы построить связь 1:N в данном случае, я предлагаю указать commentId в качестве первичного ключа таблицы photo_comment. При такой конфигурации не получится так, что один и тот же комментарий окажется подписанными к различным фотографиям.

Очень важно не забывать обеспечивать ссылочную целостность между таблицами. Поэтому мы установим для commentId внешний ключ, ссылающийся на поле id таблицы comment, а для photoId - внешний ключ, ссылающийся на поле id таблицы photo.

Аналогичным образом строятся связи "фотоальбом-комментарий"(таблица photoAlbum_comment) и "статья-комментарий"(таблица article_comment). На данный момент у нас уже имеется схема базы данных, которая позволит существовать пользователям на сайте, дать возможность выкладывать фотографии, управлять фотоальбомами, комментировать и голосовать за фотографии, фотоальбомы и статьи. Отлично. Идем дальше.

Метки

Нам осталось реализовать последнее требование - возможность привязывать теги к контенту. Обратимся к нашей диаграмме "сущность-связь"(рис. 2). Так как в нашей модели тег является отдельным типом объектов, создадим для него отдельную таблицу tag. Теги должны однозначно идентифицироваться в системе и иметь название в виде символьной строки. Поэтому в таблице tag мы создаем два поля - id(идентификатор - первичный ключ) и title(название). Теперь нужно организовать связ "тег-контент". Давайте разберем построение ассоциации "статья-тег"(ассоциации "фотоальбом-тег" и "фотография-тег" строятся аналогичным образом). Данная связь имеет тип N:M. Следовательно создадим новую таблицу с именем article_tag и добавим в нее два поля - articleId(идентификатор написанной статьи) и tagId(идентификатор метки). Для однозначной идентификации строк таблицы нужен первичный ключ. Таковым должна быть пара articleId и tagId. Данное ограничение не позволит одному и тому же тегу быть привязанным к одной взятой статье более одного раза. Далее необходимо гарантировать ссылочную целостность между таблицами. Поэтому для поля tagId таблицы article_tag мы ставим внешний ключ так, чтобы он ссылался на поле id таблицы tag, а для articleId установим внешний ключ таким образом, чтобы он ссылался на поле id таблицы article.

Аналогичным образом строются связи "фотоальбом-тег" и "фотография-тег".

Теперь у нас более или менее полная схема базы данных, которая удовлетворит всем нашим требованиям. Это уже что-то, с чего можно начинать пробывать тестировать и запускать приложение. Полученный результат не является итогом разработки базы данных, а скорее отправной точкой для ее развития и оптимизации. Нужно отметить, что в данной статье я не приводил и не раскрывал понятия нормализации и нормальных форм. На самом деле в реальной жизни ими очень редко оперируют. Ключом к построению приемлемой схемы реляционной базы данных является правильная идентификация сущностей и типов связей между ними. Остальное - дело техники. Нужно просто запомнить, с помощью каких конструкций и приемов строятся определенные типы связей и не нарушать концептуальную целостность объектов(Например, таблица user должна содержать информацию о пользователях и только о них, а photo - только о фотографиях).

Нужно сказать, что представленное решение не является единственным возможным. Для одной и той же задачи возможны несколько схем в общем случае. Автор: Павел Коломыткин Сайт: http://mywebdev. ru/

Отзывы и комментарии