Какая должна быть структура базы для системы обмена сообщениями?

Question

[Иван Иванов]

Добрый день.

Необходимо сделать внутреннюю систему обмена сообщениями похожую на электронную почту. У каждого пользователя есть виртуальный почтовый ящик и папки "Входящие", "Отправленные", "Черновики" и "Корзина".
Подскажите, пожалуйста, какая должна быть структура базы данных чтобы у отправителя сообщение отображалось в папке "Отправленные", а у получателя во "Входящие" и была возможность удалить сообщение независимо друг от друга.

Answer 1

[Николай Чуприк]

На скорую руку могу такое предложить.

пользователи:
user_id | user_name | group_id | password_hash | ...

папки пользователей:
folder_id | user_id | folder_name | parent_folder_id | folder_type

словарь типов папок (стандартные обязательные папки: "Входящие", "Отправленные") :
folder_type | default_name_ru | default_name_en

сообщения:
message_id | from_user_id | title | text | ...

получатели сообщений (возможна отправка сразу многим, если надо):
message_id | to_user_id

содержимое папок:
message_id | folder_id

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

Жирным выделил принципиальные поля, остальное — лирика. Курсивом — ключи.

Comments

[Иван Иванов]

Спасибо. Тогда, например, для получения сообщений мне будет нужно получить все папки пользователя, затем получить сообщения в них и объединить это с данными пользователя (например, получить имя отправителя). Верно?

[Николай Чуприк]

Иван Иванов, ну да, конечно.

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

[Иван Иванов]

Николай Чуприк, я думал примерно также, но не будет ли избыточным одновременная запись в таблицу "сообщения" и "содержимое папок" ведь мы можем сразу указать, что для пользователя A это будет папка "Отправленные", а для пользователя B соответственно "Входящие"?

[Николай Чуприк]

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

Теперь по вопросу. Да, при отправке сообщений они автоматически распределяются в папки "Отправленные" и "Входящие" у отправителя и получателя. Но они ведь могут потом перенести (скопировать) сообщения в другие папки. Вообще составленная мной структура БД соответствует папкам IMAP, где папки имеют древовидную структуру, а сообщение может содержаться сразу в нескольких папках (иметь ярлыки).

Если же у вас есть жёстко только две папки и всё, то тогда вообще сущность "папка" не нужна. Если я получатель, то у меня сообщение находится во "Входящих", если отправитель — то в "Отправленных". Тогда можно вообще сделать одну таблицу с сообщениями (даже без индекса message_id) и только указывать статус true/false отображения сообщения у корреспондентов (т.е. если кто-то у себя удалил, ему поставить false):

таблица сообщений:
from_user_id | to_user_id | show_in_inbox | show_in_send | ...

Просто, но нет гибкости. Но может она вам и не нужна.

UPD: Если есть ещё папки "Черновики" и "Корзина", то можно указывать в какой папке находится сообщение в отправителя и у получателя.

таблица сообщений:
from_user_id | to_user_id | author_folder | reciever_folder | ...

Значение полей author_folder, reciever_folder могут быть такими

0 — Сообщение отсутствует. Например когда оно ещё не отправлено, его нет у получателя ни в какой папке, или же сообщение окончательно удалено даже из корзины.
1 — Черновики
2 — Отправленные
3 — Входящие
4 — Корзина

UUPD:
Опять таки в author_folder, reciever_folder не надо писать названия папок: Отправленные, Входящие. Там должен быть короткий индекс папки, а сами названия папок должны согласно науке о нормальных формах БД содержаться в отдельном словаре пример, как я указал выше.

Иначе вы храните избыточную информацию, у вас в таблице сообщений будет в строках много раз повторяться слова: Входящие, Входящие, Входящие, Отправленные, Отправленные... Что явно избыточно. Кроме того, если захотите переименовать папке "Входящие" например на "Мне написали", то придётся перелопачивать всю таблицу сообщений и проводить замену. А так достаточно просто изменить название в словаре.