в свое время делали нечто похожее, "лобби для движка онлаин игры
задача была избежать хранения копий одого сообщения если оно адресованно группе юзеров, т.к. сообщения часто содержали BLOB - данные записанные игровым движком.
при отравке сообщения создается три записи:
1. сообственно само сообщение, запись в таблице msg
2. для отправителя о том что сообщение отправлено: запись в таблице mailbox (отправитель,ид сообщения, ид папки Sent, flag: unread)
3. для получателя о том что сообщение доставлено: запись в таблице mailbox (получатель, ид сообщения, ид
папки Inbox, flag: unread).
После прочтения сообщения отправитель и/или получатель меняют unread|read флаг на соответсвующей записи в mailbox.
На удаление запись "переносится" в папку Deleted.
Записи в mailbox и в msg никогда не удаляются. Планировалось отдельно прикрутить систему удаления через триггер и таблицу заданий на удаление сообщений, но на этапе разработки решили не делать, т.к. было полно другой работы.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
-- messages
CREATE TABLE msg (
id serial NOT NULL, -- id of the msg
from_user_id integer NOT NULL, -- from whom the msg was sent
to_user_id integer NOT NULL, -- id, to whom the msg was sent
inreplyto_id integer NOT NULL DEFAULT currval('msg_id_seq'::regclass), -- msg threads feature.
...
... -- subject, body, flags (urgent, normal, bulk), other message attributes
...
timestamp timestamp without time zone NOT NULL DEFAULT now(),
);
-- mailboxes
CREATE TABLE mailbox
(
owner_id integer NOT NULL, -- owner of the mailbox
msg_id integer NOT NULL, --
folder_id integer NOT NULL, -- msg folder in the mailbox, like inbox, sent, deleted
status_id integer NOT NULL, -- msg status in the mailbox, like new, read, etc
...
... -- other flags and attributes
...
CONSTRAINT mailbox_pkey PRIMARY KEY (owner_id, msg_id),
...
)
