Проектирование системы сообщений (схемы базы данных)

Question

[Глеб Старков]

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

На первый взгляд все понятно, выглядит примерно так:
Кто, кому, тема, сообщение, в корзине?
(там есть и другие поля, типа просмотрено или новое, но это не суть)

Но одно сообщение относится же к двум пользователям, оба видят его у себя либо во входящих, либо в исходящих, либо в корзине, поэтому дальше проектирую так:

Кто, кому, тема, сообщение, from_trash, to_trash.

Вроде все ок, но теперь встает вопрос, а как очистить корзину.
Удалить полностью сообщение нельзя, так как удалится у обоих.

Сначала начал было так:
...., from_trash, to_trash, from_del, to_del

Но это же вообще уже некрасиво, туча флагов…

Думаю, может сделать поле, типа статус:
1 — видят оба,
2 — у автора в корзине, у адресата нет
3 — в корзине у обоих
4 — автор удалил вообще, у адресата в корзине
И так далее, вариантов куча, блин, тоже не красиво, а значит — не вариант.

Вот и вопрос, — как правильно спроектировать схему данных для этой задачи?
И еще вопрос, — может где-то есть паттерны для таких вещей, чтобы не изобретать велосипеды.

Answer 1

[baadf00d]

я вижу два варианта решения: простое и правильное.
Простое — это создавать по сообщению на отправителя и получателя. Тогда они становятся изначально независимыми.
Правильное — это то к чему вы почти пришли: сделать таблицу папок, таблицу сообщений и таблицу-связку: сообщение-папка-юзер.
таблица folder: id|name
таблица message: id|date|title|text и тп
связка: id|userId|folder|state|messageId + я бы добавил уникальный индекс по userId|messageId чтобы обеспечить целостность
state — состояние сообщения для пользователя: новое, прочитанное, в треше, удаленное на совсем
единственный нюанс тут — выборка для папки треш должен делаться по статусу а не по таблице папок

если у юзера могут быть свои папки, то структура должна быть немного иной: таблица папок будет ссылаться на юзера, а в связке userId нужно исключить.

Comments

[Глеб Старков]

Да, фактически на этом варианте и остановился.
Только не делал таблицу folder, так как пока предполагается два основных типа папок — исходящие и входящие, и в таблице связке это номера 1 и 2 (соответственно, если структуру папок нужно будет расширить, можно легко реорганизовать эту схему до трех-табличной).
Остальное — идентично.
Спасибо всем.

Answer 2

[afiskon]

Вот, может пригодится кому-нибудь, кто зайдет сюда из гугла например: eax.me/database-design

Comments

[olegilutin]

Зашёл. Пригодилось.

Answer 3

[Глеб Старков]

Пока склоняюсь к такой схеме:

table message:
id | subject | body |… other… | user_id
— здесь user_id — отправитель, он всегда один, в отличие от получателей

table message_user_rel (связь сообщения с пользователями)
id | message_id | type (входящее, исходящее) | trash (корзина)

Но здесь тоже проблема:
Если сообщение удалит получатель, то при выборке исходящих для отправителя невозможно определить, кто получатель (сообщение удалено)

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