Модель данных для простой системы сообщений.

Bermuda · 10 Июл 2008

Модель данных для простой системы сообщений.

"Рисую" модель данных для системы сообщений между двумя пользователями. Пытаюсь определить необходимые сущности. Уже ясно, что будет сущность "сообщение". Поскольку требуется возможность вывода сообщений цепочками, то понадобится сущность "цепочка" со связью один ко многим с сообщениями. Цепочка линейная (не древовидная), сообщения "группируются" по теме и сортируются по времени.

Затык произошел на этапе удаления сообщений. Если получатель удалит сообщение, то отправитель его также лишиться. Вижу несколько вариантов.

А. Сохранять одну копию сообщения указывая отправителя и получателя.
Сущности: цепочка, сообщение.
В таком случае непонятно как удалить сообщение так, чтобы оно не пропало на другом конце.

Б. Сохранять две копии сообщения -- одну для отправителя, другую для получателя.
Сущности: цепочка, сообщение.
Уж не знаю почему, но не нравится мне этот вариант. Слишком уж избыточно.

В. Упразднить сущность "сообщение" и ввести две новые сущности "входящее сообщение" и "исходящее сообщение". Также придется сохранять две копии -- одну в исходящих, другую во входящих.
Сущности: цепочка, входящее сообщение, исходящее сообщение.
Концептуально может оно и правильно, но как потом показывать цепочку сообщений? Смешивать, а потом сортировать две разные сущности? Снова как-то избыточно, да и сущности практически идеентичны.

Г. Ввести сущность "очередь сообщений". Попробую объяснить.

Таблица "очередь сообщений"
- идентификатор пользователя
- идентификатор сообщения
- флаг входящее/исходящее

При создании сообщения создается две записи в таблице "очередь сообщений". Одна с идентификатором отправителя, идентификатором сообщения и флагом "исходящее", вторая запись с идентификатором получателя, идентификатором сообщения и флагом "входящее".
Сущности: цепочка, очередь сообщений, сообщение.
Таким образом сообщение хранится в единственной копии, имеется возможность вывести цепочку сообщений, каждый пользователь может независимо удалять сообщения путем удаления записи из таблицы "очередь сообщений". Появляется проблема, если получатель и отправитель удалили сообщения, то в таблице "очередь сообщений" не останется записей, а в таблице сообщений останутся осиротевшие сообщения. Т. е. этот момент нужно контролировать программно. Как только удаляется последняя ссылка на сообщение в таблице "очередь сообщений", то удаляется и само сообщение. Тоже получается как-то через опу.

Пожалуйста, подкиньте идею. Может кто уже "рисовал" модель данных для такой задачи.

berkut · 10 Июл 2008

многа букаф(

-~{}~ 11.07.08 01:04:

я стырил с ипб форума:
[sql]
CREATE TABLE `pm` (
`id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`sender` MEDIUMINT(9) UNSIGNED NOT NULL DEFAULT '0',
`subject` VARCHAR(20) COLLATE utf8_general_ci NOT NULL DEFAULT '',
`message` TEXT COLLATE utf8_general_ci NOT NULL,
`date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
`flags` SET('system') NOT NULL,
PRIMARY KEY (`id`);

CREATE TABLE `pm_recipients` (
`recipient` MEDIUMINT(9) UNSIGNED NOT NULL DEFAULT '0',
`message_id` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
`folder_id` TINYINT(4) UNSIGNED NOT NULL,
`readed` DATETIME DEFAULT NULL,
UNIQUE KEY `rcpt_mes` (`recipient`, `message_id`),
KEY `recipient` (`recipient`),
KEY `message_id` (`message_id`)

)ENGINE=MyISAM[/sql]
чота мне приглянулась струхтура

)ENGINE=MyISAM

A1x · 11 Июл 2008

например добавляем в табл. сообщений два поля: sender_box receiver_box
при создании сообщения значения этих полей ставим соответственно 'sent', 'inbox'
при удалении сообщения например получателем receiver_box становится 'deleted'
когда оба поля 'deleted' - сообщение можно удалить из таблицы

а букаф таки да, многа

Bermuda · 11 Июл 2008

atv · 11 Июл 2008

Мы юзаем таую структуру. Подходит как для варианта А, так и для варианта Б. Мы используем вариант Б.
[sql]
CREATE TABLE `sfcClient` (
`cId` int(11) NOT NULL AUTO_INCREMENT,
`cLogin` varchar(20) DEFAULT NULL,
`cPassword` varchar(50) DEFAULT NULL,
`cFirstName` varchar(30) DEFAULT NULL,
`cLastName` varchar(30) DEFAULT NULL,
`cMiddleName` varchar(30) DEFAULT NULL,
`cCompany` varchar(255) DEFAULT NULL,
`cPosition` varchar(100) DEFAULT NULL,
`cEmail` varchar(100) DEFAULT NULL,
`cPhone` varchar(25) DEFAULT NULL,
PRIMARY KEY (`cId`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `sfcClientContact` (
`ccId` int(11) NOT NULL AUTO_INCREMENT,
`ClientId` int(11) NOT NULL,
`ccRecipientId` int(11) NOT NULL,
PRIMARY KEY (`ccId`),
UNIQUE KEY `ClientId` (`ClientId`,`ccRecipientId`),
KEY `ccRecipientId` (`ccRecipientId`),
KEY `ClientId_2` (`ClientId`),
CONSTRAINT `FK_ccRecipientId` FOREIGN KEY (`ccRecipientId`) REFERENCES `sfcClient` (`cId`),
CONSTRAINT `FK_Client_Contact` FOREIGN KEY (`ClientId`) REFERENCES `sfcClient` (`cId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `sfcClientMessage` (
`cmId` int(11) NOT NULL AUTO_INCREMENT,
`ClientId` int(11) NOT NULL,
`cmSenderId` int(11) NOT NULL,
`cmRecipientId` int(11) NOT NULL,
`cmSubject` varchar(100) NOT NULL,
`cmMessage` text NOT NULL,
`cmSendDate` datetime NOT NULL,
`cmIsRead` tinyint(4) NOT NULL DEFAULT '0',
`cmIsDeleted` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`cmId`),
KEY `ClientId` (`ClientId`),
KEY `cmSenderId` (`cmSenderId`),
KEY `cmRecipientId` (`cmRecipientId`),
CONSTRAINT `FK_Client_Message` FOREIGN KEY (`ClientId`) REFERENCES `sfcClient` (`cId`),
CONSTRAINT `FK_cmRecipientId` FOREIGN KEY (`cmRecipientId`) REFERENCES `sfcClient` (`cId`),
CONSTRAINT `FK_cmSenderId` FOREIGN KEY (`cmSenderId`) REFERENCES `sfcClient` (`cId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
[/sql]

Bermuda · 11 Июл 2008

По ходу я перемудрил с русским языком

. Переведу на "человеческий".

Вариан А.
[SQL]CREATE TABLE `messages` (
`id` int auto_increment,
`id_thread`int,
`id_sender`int,
`id_recepient`int,
`message` varchar,
PRIMARY KEY (`id`),
KEY `FK_messages` (`id_thread`),
CONSTRAINT `FK_messages` FOREIGN KEY (`id_thread`) REFERENCES `threads` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB;
[/SQL]

PHP:

messages
id    id_thread    id_sender    id_recepient    message 
1        1             1               2        Hi Bob! How are you? 
2        1             2               1        Hi Jim! I&#180;m just fine. 
3        2             2               1        Hey Jim! It seems that messaging system works!

[SQL]CREATE TABLE `threads` (
`id` auto_increment,
`subject` varchar,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
) ENGINE=InnoDB;[/SQL]

PHP:

threads
id   subject 
 1   Foo
 2   bar

В этом случае при удалении сообщения на одном конце, оно пропадает на другом.

Вариант Б.
[SQL]CREATE TABLE `messages` (
`id` int auto_increment,
`id_thread` int,
`id_user` int,
`flag_inbound` int,
`message` varchar,
PRIMARY KEY (`id`),
KEY `FK_messages` (`id_thread`),
CONSTRAINT `FK_messages` FOREIGN KEY (`id_thread`) REFERENCES `threads` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB;[/SQL]

PHP:

messages
id    id_thread id_user flag_inbound  message 
 1         1         1        0         Hi Bob! How are you?
 2         1         2        1         Hi Bob! How are you?
 3         1         2        0         Hi Jim! I&#180;m just fine.
 4         1         1        1         Hi Jim! I&#180;m just fine.
 5         2         2        0         Hey Jim! It seems that messaging system works!
 6         2         1        1         Hey Jim! It seems that messaging system works!

[SQL]CREATE TABLE `threads` (
`id` int auto_increment,
`subject` varchar default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;[/SQL]

PHP:

threads
id   subject 
 1   Foo
 2   bar

Уж не знаю почему, но не нравится мне этот вариант. Слишком уж избыточно.

Вариант В.
[SQL]CREATE TABLE `inbox` (
`id` int auto_increment,
`id_thread` int,
`id_owner` int,
`id_sender` int,
`message` varchar,
PRIMARY KEY (`id`),
KEY `FK_messages` (`id_thread`),
CONSTRAINT `FK_messages` FOREIGN KEY (`id_thread`) REFERENCES `threads` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB;[/SQL]

PHP:

inbox
id id_thread id_owner id_sender message 
 1      1          2        1    Hi Bob! How are you?  
 2      1          1        2    Hi Jim! I&#180;m just fine. 
 3      2          1        2    Hey Jim! It seems that messaging system works!

[SQL]CREATE TABLE `outbox` (
`id` int auto_increment,
`id_thread` int,
`id_owner` int,
`id_recipient` int,
`message` varchar,
PRIMARY KEY (`id`),
KEY `FK_messages` (`id_thread`),
CONSTRAINT `FK_messages` FOREIGN KEY (`id_thread`) REFERENCES `threads` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB;[/SQL]

PHP:

outbox
id id_thread id_owner id_sender message 
 1      1          1        2    Hi Bob! How are you?  
 2      1          2        1    Hi Jim! I&#180;m just fine. 
 3      2          2        1    Hey Jim! It seems that messaging system works!

[SQL]CREATE TABLE `threads` (
`id` int auto_increment,
`subject` varchar,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `id_2` (`id`)
) ENGINE=InnoDB;[/SQL]

PHP:

threads
id   subject 
 1   Foo
 2   bar

Концептуально может оно и правильно, но как потом показывать цепочку сообщений? Смешивать, а потом сортировать две разные сущности? Снова как-то избыточно, да и сущности практически идеентичны.

Вариант Г.
[SQL]CREATE TABLE `messages` (
`id` int auto_increment,
`id_thread` int,
`message` varchar,
PRIMARY KEY (`id`),
KEY `FK_messages` (`id_thread`),
CONSTRAINT `FK_messages` FOREIGN KEY (`id_thread`) REFERENCES `threads` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB;[/SQL]

PHP:

messages
id id_thread message 
 1    1       Hi Bob! How are you? 
 2    1       Hi Jim! I&#180;m just fine. 
 3    2       Hey Jim! It seems that messaging system works!

[SQL]CREATE TABLE `messages_spool` (
`id_message` int,
`id_user` int,
`flag_inbound` int,
KEY `FK_messages_spool` (`id_message`),
CONSTRAINT `FK_messages_spool` FOREIGN KEY (`id_message`) REFERENCES `messages` (`id`) ON UPDATE NO ACTION
) ENGINE=InnoDB;[/SQL]

PHP:

messages_spool
id_message id_user flag_inbound 
      1            1         0 
      1            2         1 
      2            2         0 
      2            1         1 
      3            2         0 
      3            1         1

[SQL]CREATE TABLE `threads` (
`id` int auto_increment,
`subject` varchar,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `id_2` (`id`)
) ENGINE=InnoDB;[/SQL]

PHP:

threads
id   subject 
 1   Foo
 2   bar

Появляется проблема, если получатель и отправитель удалили сообщения, то в таблице "очередь сообщений" не останется записей, а в таблице сообщений останутся осиротевшие сообщения. Т. е. этот момент нужно контролировать программно. Как только удаляется последняя ссылка на сообщение в таблице "очередь сообщений", то удаляется и само сообщение. Тоже получается как-то через опу.

Надеюсь так будет понятнее.

kode · 11 Июл 2008

pmbox:

id | type [in/out] | message_id | from | to | readed

1 | from | 1 | 10 | 12 | 1
2 | to | 1 | 12 | 10 | 1
3 | from | 2 | 12 | 10 | 1
4 | to | 2 | 10 | 12 | 0

pm :
id | text | title | date | etc .....

1 | Hello bobby | Hi! | 2008-02-01 | ....
2 | Hey, guy! Who are you? | WTF? | 2008-02-02 | ...

когда пользователь удаляет у себя сообщение - она удаляется из pmbox, если оба пользователя удалили сообщение - удаляем и из pm

Bermuda · 11 Июл 2008

kode
Это вариант "Г". В одном проекте я так и делал. Приходится делать еще одно дополнительное действие -- проверять, удалили ли оба пользователя сообщение и удалять его.

kode · 11 Июл 2008

Автор оригинала: Bermuda
kode
Это вариант "Г". В одном проекте я так и делал. Приходится делать еще одно дополнительное действие -- проверять, удалили ли оба пользователя сообщение и удалять его.

Знaешь для чего придумали хранимые процедуры?

Bermuda · 11 Июл 2008

kode
Процедуры процедурами, а подходящую модель данных наверняка можно "нарисовать". Пока не догоняю как.

spiverg · 14 Июл 2008

У меня такая структура

Код:

CREATE TABLE message (
    message_id SERIAL NOT NULL PRIMARY KEY, -- идентификатор сообщения
    message text NOT NULL, -- текст сообщения
    date0 TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(), -- дата создания
    ip inet NOT NULL, -- ip отправителя
    author_id INTEGER REFERENCES siteuser, -- автор (отправитель сообщения), внешний ключ на siteuser
    owner_id INTEGER REFERENCES siteuser, -- владелец (получатель сообщения), внешний ключ на siteuser
    read_flag BOOLEAN NOT NULL DEFAULT FALSE, -- указывает прочитал ли владелец сообщение
    author_del_flag BOOLEAN NOT NULL DEFAULT FALSE, -- указывает удалил ли автор сообщение
    owner_del_flag BOOLEAN NOT NULL DEFAULT FALSE -- указывает удалил ли владелец сообщение
)

сообщение удаляется окончательно когда его удалили и автор и владелец, иначе устанавливается соответсвующий флаг

Bermuda · 14 Июл 2008

spiverg
Спасибо. Но есть проблема. Когда пользователь удаляет сообщение, то должна удаляться запись, а не изменяться флаг.

spiverg · 14 Июл 2008

Bermuda
Объясни мне если я не понимаю, но я не вижу проблемы.
При удалении сообщения, запись будет удаляться, если его партнер(отправитель или получатель) тоже "удалил" это сообщение, в противном случае ставится флаг.

При выборке во входящих и исходящих нужно учитывать флаги.

p.s. или это идеологические муки?, раз пользователь удалил то запись должна удалиться

Bermuda · 14 Июл 2008

spiverg
Идеологические.

korchasa · 14 Июл 2008

Автор оригинала: Bermuda
spiverg
Спасибо. Но есть проблема. Когда пользователь удаляет сообщение, то должна удаляться запись, а не изменяться флаг.

Глупость. Если я не удалял сообщение, то оно не должно пропасть.

-~{}~ 14.07.08 12:54:

И в любом случае, это не в ответственности программиста, это бизнес.

Bermuda · 14 Июл 2008

korchasa
А при чем здесь программист? При чем здесь бизнес?

korchasa · 14 Июл 2008

Автор оригинала: Bermuda
korchasa
А при чем здесь программист? При чем здесь бизнес?

Просто такие решения(удалять, или нет) надо принимать не с точки зрения программиста, а с точки зрения пользователя.

ЗЫ: А зачем вам база для сообщений?

Bermuda · 14 Июл 2008

См. выше.
"Рисую" модель данных для системы сообщений между двумя пользователями.

korchasa · 14 Июл 2008

Автор оригинала: Bermuda
См. выше.
"Рисую" модель данных для системы сообщений между двумя пользователями.

Перефразирую: зачем использовать РСУБД, при условии, что все выборки можно свести к уникальному ключу, и спокойно использовать файлы?

Bermuda · 14 Июл 2008

korchasa
Сложный вопрос. Наверное затем, чтобы база данных для начала была нормализована.

Модель данных для простой системы сообщений.

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

never knows best

Новичок

never knows best

Новичок

Новичок

Новичок

Новичок

Новичок

LIMB infected

Новичок

LIMB infected

Новичок

LIMB infected

Новичок