Как спланировать БД (чат)?

Question

[Artem0071]

Есть 2 таблицы:
users (id, group_id, name)
groups (id, title)

У каждого пользователя может быть только 1 группа, либо не быть вовсе
Любой пользователь может писать группе, группа может писать пользователям
Группа пишет пользователю, но через пользователя привязанного к этой группе

Допустим есть Группа1 (1,'group1'), Пользователь1(1,1,'first_user'), Пользователь2(2,NULL,'second_user')

Пользователь2 пишет в Группа1
Но группа сама по себе не может ответить, должен ответить пользователь (а-ля вк)

То есть должен зайти Пользователь1 в группу, перейти в чат(от группы) и ответить пользователю2

При этом еще пользователь может писать пользователю, группа группу, пользователь администратору

В общем такая вот хитрая система

Проблема в том, что я делаю все как сложно

сделал chats (id,first_user,first_group,second_user,second_group,admin)
Таким образом если пользователь пишет группе, то работают first_*
если пользователь пользователю, то *_user

Но проблема в том, как сделать еще сами сообщения messages
мой план - messages(id,user_creator,user_id,group_id,admin, text)
То есть в любом случае записывается user_creator
а так же если сообщение от пользователя, то user_id, если от группы, то group_id

В общем как то сложно как мне кажется. должен же быть способ по проще?

Вот пока что так

Comments

[Philipp]

Т.е. пользователь может писать в чат сообщение как от своего имени, так и от имени группы (как посты на странице в вк)?

[Artem0071]

Philipp, да)

Answer 1

[Philipp]

Итак, что вам нужно. Предполагается, что у вас есть табицы user и group.

Таблица со списом чатов

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

Список участников чата

roster
	chat_id - идентификатор чата
	user_id - идентификатор пользователя
	group_id - ид группы, если пользователь пишет от имени группы или NULL, если от пользователя (можно даже держать 2 записи, где group_id = null и где нет)

Список сообщений

messages
	id - идентификатор сообщения
	chat_id - идентификатор чата
	user_id - ид пользователя
	group_id - ид группы, если сообщение отправлено от имени группы или NULL, если от пользователя
	text - текст сообщения

Статусы сообщений

message_status
	message_id - идентификатор сообщения
	user_id - идентификатор пользователя
	read - прочтено или нет
	notified - отправлено уведомление о сообщении или нет

Итак, как это работает.
Круг, в котором общаются пользователи называется чатом (chat).
Кто находится в чате определяется через ростер (roster). Ростер может не быть показан в интерфейсе.
Кто кого приглашает в чат определяется через бизнес-логику приложения.

В данной схеме сценарий "Пользователь2 пишет в Группа1" выглядит так.
Создается чат, далее в ростер добавляются П2 и Г1. Далее просто создается сообщение от имени П2. Через бизнес-логику находится владелец Г1 и ему отправляется уведомление.