Пример создания структуры базы данных (на примере социальной сети)

Введение

В этой статье разберем пример создания структуры базы данных для социальной сети. 

У нас нет задачи создать полноценную структуру для всех объектов базы, основная идея - показать как выделять объекты и как их связывать, а также показать некоторые тактические моменты по созданию структуры БД. 

Описание таблицы БД будет в следующем формате: 

Название таблицы - перечень столбцов через запятую.

Во всех таблицах первичный ключ это id int identity(1,1). 

Внешние ключи обозначаются как elementID int (суффикс ID подсказывает нам что это внешний ключ на другую таблицу). Это просто наше соглашение для изменования полей. 

Шаг 1. Выделение объектов базы данных

Откройте свою страницу в ВК и самостоятельно посмотрите какие объекты можно выделять. 

Что есть объект - это некая независимая сущность, информацию о которой мы можем хранить в базе данных. 

Я вижу следующие объекты, я сразу их разделю на подгруппы:

Общие  

• Пользователь (users)
• Сообщение на стене (posts)

Группы 

• Группа  (groups)
• Участник группы (groupMembers)
• Сообщение в группе (posts)

Галерея

• Альбом (albums)
• Элемент альбома (albumItems) 

Чаты 

• Сообщение (messages)
• Комната в чате (rooms)

Этим пока ограничимся (не берем видео, аудио, рекламу и т.д.).  

Сразу пишем название будущей таблицы (мы используем практически всегда название во множественном числе). 

Шаг 2. Определяем более детально объекты 

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

Особую важность имеют именно связи, т.к. их потом гораздо сложнее менять - важно сразу правильно определить тип связи (один ко одному, один ко многим, много ко многим). 

Один ко одному

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

Один ко многим

Есть у нас сотрудники, у каждого есть определенная должность. Какая связь между должностями и сотрудниками. Можно мыслить следующим образом: Сотрудник может иметь 1 должность. Конкретную должность может занимать несколько сотрудников - значит связь 1 ко многим. 

Как она реализуется? С помощью внешнего ключа в таблице humans: 

• posts(id, name) - должности
• humans (id, fio, postID) - сотрудники.

Много ко многим

Допустим есть у нас студенты и предметы. Один студент изучает множество предметов. Один предмет изучают множество студентов. Это связь много ко многим. 

В этом случае необходимо добавление дополнительной таблицы связи, где будут указаны внешние ключи на 2 таблицы:

• students (id, fio)
• subjects (id, name)
• studentSubjects (id, studentID, subjectID)   - мы называем таблицу Предметы студента (т.к. это соответствует реальному миру), но бывают и другие названия, например, studentsSubjects (на мой взгляд, более кривое название). 

Детализация структуры БД

Теперь вернемся к структуре БД социальной сети: 

• users (id, fio, sex ... ) Пользователь - это центральная сущность, у нее может быть куча полей (исходя из профиля и настроек). Здесь мы указываем только самые базовые. 
• messages (id, text, created, userID FK, roomID) Сообщения содержат ссылку на автора сообщения и комнату, где написано это сообщение. 
• roomTypes (id, code, name) - типы комнат. Комнаты могут быть привязаны к каким-то объектам (группа, переписка личная и т.д.)
• rooms (id, typeID, itemID, created, [some settings for room]) - определяется к чему привязана комната через связку (typeID, itemID), когда создана. 
• roomUsers (id, userID, roomID, created) - кто в комнате (чате) находится. Это связь много ко многим между Пользователями и Комнатами. 

Если мы хотим фиксировать кто когда был добавлен и удален в комнате, то делаем дополнительную таблицу

• roomUsersLog (id, roomID, userID, created, operation), где operation - это либо add либо delete к примеру. 

Разберемся с группами

• groups (id, name, ownerID, desc) - у группы есть владелец (это ссылка на users - один ко многим, у группы может быть только один владелец, а у юзера может быть несколько групп, где он владелец).
• groupMembers (id, groupID, userID, created, roleID) - какой юзер в какой группе с какой ролью (можно было бы кстати задать владение группой через это поле, тогда можно было бы сделать что у группы может быть несколько владельцев). 
• groupMemberRoles (id, name, code) - роль пользователя в группе (админ, редактор, пользователь). Зачем поле code - для возможного обращения по нему к конкретной роли. Обращение по id из кода - это не очень хороший ход, т.к. при переносе данных из одной базы в другую id могут быть другие и это может сломать работу компонента. 

Давайте разбираться с постами

Можно прикрепить посты прямо к группе, но у нас же есть еще личные страницы + в будущем могут еще появиться другие объекты, которым нужна стена. 

Поэтому вводим такое понятие как Стена и уже его цепляем к нужному нам объекту. Делается это с целью не плодить кучу однотипных таблиц (посты, комментарии для группы ничем не отличаются от постов и комментариев для личной стены).

• wallTypes (id, name, code) - тип стены (личная или группы)
• walls (id, typeID, itemID) - стена задается типом и идентификатором стены (по сути это ссылка на id либо группы, либо юзера. Да, это не чистый внешний ключ (как было бы в случае отдельных таблиц), но зато достигается универсальность в плане хранения данных).  Другой вариант мог бы быть хранение groupID и userID (т.е. если groupID не null - тогда это привязка к группе), но в этом случае нам придется менять структуру таблицы при появлении нового типа и что самое плохое - учитывать это изменение где-то в коде. 
• posts (id, userID, created, wallID, text, repostID, likeCount) - это стена группа, ее посты. Автор userID. Связка с группой один ко многим (после принадлежит только одной группе).  repostID - это случай, когда наш пост является репостом другого поста. likeCount - количество лайков. 
• postComments (id, userID, created, postID, parentCommentID, likeCount) - указываем кто оставил коммент, когда, на какой пост. Если это комментарий к комментарию, то будет указан родительский комментарий. Также собираем агрегированное количество лайков в отдельном поле. 
• likeTypes (id, name, code) - к чему мы ставим лайк (коммент, пост или что-то еще). 
• likeLog(id, typeID, itemID, value, created, userID) - лог лайков, кто когда поставил лайк на какой объект. 

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

Когда проектируете связи таблиц, обязательно думайте как это будет использоваться на практике. Например, можно в postComments хранить только для корневых комментов поле postID, а вложенные же можно достать по parentCommentID. На практике это очень плохо будет работать, т.к. вместо того, чтобы просто быстро по postID извлечь все комменты одним махом, вам придется пробегать по всему дереву комментариев в каждом посте. Это большие и ненужные затраты мощностей сервера. Поле postID необходимо ставить в каждом комментарии для быстрого извлечения комментариев на пост. 

Разберем галерею фото

Можно рассматривать фото как пост и применить всю структуру данных к картинкам. По сути в этом есть смысл - у фото тоже есть комментарии, тоже есть лайки, есть автор. 

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

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

• albums (id, name, desc, created, userID, ord, coverImageID) -  альбомы с названием, когда создан, кто владелец. ord задает порядок вывода.  coverImageID задает ссылку на обложку. 
• albumImages (id, name, img, thumb, desc, albumID, created, likeCount, ord) - картинки в альбоме

Для хранения лайков мы можем задействовать таблицу likeLog (в эти моменты как раз и начинаешь ценить универсальный подход с typeID, itemID). 

Заметим, что в данной структуре не получится хранить фото без альбома (т.е. albumID задает связь с владельцем фото). 

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

Поле ord типа int задает порядок размещения фото в альбоме. 

Кого-то может смутить такая зацикленная связь: albums ссылается на albumImages через coverImageID, а картинки ссылаются на альбомы через albumID. Тут лучше просто рассматривать ключи полностью отдельно. 

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

Нет ли здесь проблемы курицы и яйца? Нет. Мы создаем сначала альбом с coverImageID = NULL. Когда создаем первое фото в альбоме, то идет проверка: если не было еще фото, то у альбома тогда проставим coverImageID. 

Либо другая может быть логика - создаем просто фото. Если альбом не был выбран из существующих, то создаем альбом Альбом 1 с coverImageID = NULL, затем создаем фото с новым albumID и после этого устанавливаем coverImageID, равным id новой картинки.

Шаг 3. Самостоятельное дополнение полей и объектов.

Мы, конечно, много чего не учли в этой структуре БД: 

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

И это мы говорим только о внешней части. А есть еще и внутренняя: 

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

Попробуйте сделать следующее: самостоятельно изначально постройте свою структуру БД с учетом всех объектов, которые обозначили в этой статье. 

Детализируйте их настолько, сколько позволяет вам внешний интерфейс социальной сети. 

Заключение

Основная цель статьи - дать понимание как выделять объекты, а также правильно определять тип связи. Это в итоге дает возможность правильно определить структуру базы данных.

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

Дополнительные материалы

1. Из чего состоит веб-платформа Falcon Space
2. Установка веб-платформы Falcon Space на сервер/хостинг
3. Настройки системы
4. Пример создания системы по учету Кадры (HR) на сайте
5. Соглашения по SQL коду в платформе