Проектирование почтового ящика в соц. сети

[goodw]

Здравствуйте!

Условия задачи :
1. Высоконагруженный сервис (социальная сеть)

2. Пользователи могут обмениваться сообщениями
2.1. У каждого пользователя есть папка ВХОДЯЩИЕ
2.2. И папка ОТПРАВЛЕННЫЕ.
2.3. В списках 2.1. и 2.2. выделяются НЕПРОЧИТАННЫЕ сообщения.

3. Сообщение имеет формат -[quote автор] [Заголовок][Текст][Отправитель][Получатель][Дата][Прочтено\Нет] [/quote]


Вопрос: какая структура была бы оптимальна для хранения и работы с сообщениями?

Планируемые запросы:
1. Вывод списка ВХОДЯЩИХ сообщений (постранично)
2. Вывод списка ОТПРАВЛЕННЫХ сообщений (постранично)
3. ПОИСК сообщений среди ВХОДЯЩИХ и ОТПРАВЛЕННЫХ по ЗАГОЛОВКУ и ТЕКСТУ
4. Отметка выбранных как прочитанных и непрочитанных.
5. Удаление выбранных.
6. Отправка сообщения.


Первое, что приходит на ум:

1. Таблица сообщений - Message

[CSV]id, subject, body, senderid, receiverid, sendate, isread[/CSV]

2. Таблица почтового ящика - Mailbox

[CSV]id, messageid, userid[/CSV]

Соответственно при отправке сообщения мы создаем 2 записи: 1 в Message, другую в Mailbox на userid-владельца ящика.

При удалении сообщения мы удаляем сначала запись в Mailbox, затем, если у сообщения не лежит в ящике отправителя - удаляем его из Message.

При выводе списков делаем join - Mailbox->Message

Вопрос: можно ли это реализовать как-то более удачно, с наименьшей избыточностью и потерей производительности?

[mr.SPD]

А не проще в первую таблицу добавить userid, и избавиться от двойного инсерта при вставке и лефтджоина при выборке? Кроме этого реализуя поиск, вам придется каждый раз делать лефтджоин, чтобы узнать какие именно сообщения кому принадлежат. По мне так две таблицы красиво, но не целесообразно

[SindBad]

Вторая таблица имхо вообще не нужна. При удалении сообщения достаточно просто ставить senderid или receiverid в 0 (если оба в 0, то удалять запись).

[Big_Shark]

2 таблица лишняя.
Добавляем в первую user_id и все

P.S.
За поля senderid, receiverid нада по рукам бить
Лучше sender_id, receiver_id вы ведь не пишете все слова слитно?

[goodw]

Дополню условия:

1. Если отправитель удаляет у себя сообщение в ИСХОДЯЩИХ, оно остается у получателя во ВХОДЯЩИХ.
2. Если получатель удаляет у себя сообщение во ВХОДЯЩИХ, оно остается у отправителя в ИСХОДЯЩИХ.

Таким образом, если мы будем выставлять 0 в sender_id или receiver_id - мы скроем отправителя или получателя сообщения, ведь в списке входящих и исходящих отображается "кому" и "от кого"

Если в дополнительном поле user_id предполагаем хранить id юзера, в ящике которого это сообщение находится, вопрос: надо же отображать его и в ящике отправителя и в ящике получателя (п.1 и п.2)

Тоесть это либо практически одинаковых записи в БД: одно сообщение по user_id к отправителю, другое к получателю. Либо еще одно дополнительное поле наподобие user_id. Вот их уже поочередно можно обнулять и удалять в итоге все сообщение.

[goodw]

А если сообщение отправлено нескольким адресатам?

[denis909]

mailboxes: id, message_id, sender_id, receiver_id, send_date, read_date, is_deleted_for_sender, is_deleted_for_reciever
messages: message_id, message_body, message_title

если говорить о производительности, то возможно дублировать title в maiboxes, и по возможности запросы надо делать не джойном а простым перекрещиванием.

[goodw]

Честно говоря,

а почему бы sender_id не отправить в таблицу Message и send_date - туда же. Для одного письма при разных отправителях они будут постоянны.

[xoma]

Приходилось делать что-то подобное, правда не скажу, что проект был высоко нагруженным.
Все сообщения хранились в одной табличке, как и писали выше, а для удаления были введены два поля deletedBySender и deletedByReciv....работает оно и работает, проблем не было

[denis909]

Честно говоря, а почему бы sender_id не отправить в таблицу Message и send_date - туда же. Для одного письма при разных отправителях они будут постоянны.

я так понял, что под sender_id подразумевался receiver_id, т.к. постоянен будет именно он. его не нужно отправлять в messages:
- чтобы вести рассылку, где письмо есть шаблон
- чтобы работало быстрее, т.к. чтобы получить коль-во писем в ящике запрос будет только к одной таблице.

send_date в рассылке скорее всего должен быть датой отправки, и он будет разный, хотя это вопрос спорный.

[goodw]

Нет, под sender_id подразумевается именно Id Отправителя.

Тоесть, ОДИН отправитель шлет ОДНО письмо МНОГИМ получателям. Какой смысл тогда дублировать для каждого отправителя еще и Id получателя, если и так ясно, что для этого письма он один и тот же.

send_date в рассылке скорее всего должен быть датой отправки, и он будет разный

Почему разный? ОДНО письмо отсылается ОДНОВРЕМЕННО нескольким получателям. Разве, что участники могут быть в разных временных зонах, но и это необязательно решать на уровне БД.

[goodw]

Вобщем так.

1. Можно сделать в виде одной таблицы с дополнительными полями: is_del_for_sender, is_del_for_reciever

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

2. Или же сделать все таки с двумя: message и mailbox. Но тогда прийдется терять производительность на соединении данных из двух таблиц.

[mc-bear]

За поля senderid, receiverid нада по рукам бить

а тем кто придумал camelCase, Вы что готовы сделать?

[SindBad]

За поля senderid, receiverid нада по рукам бить
Лучше sender_id, receiver_id вы ведь не пишете все слова слитно?

Ну вот, сразу бить. Я просто очень быстро писал)) и вообще - здесь главное суть, а она не пострадала;)

[Big_Shark]

За поля senderid, receiverid нада по рукам бить

а тем кто придумал camelCase, Вы что готовы сделать?

Ну camelCase какой не какой способ выделения
Согласитесь senderId или senderID выглядят в любом случае лучше чем senderid
Да и у стиля оформления camelCase есть приписка что не рекомендовано начинать новые слова с некоторого количества букв.
В этот список букв также входит буква "i"

[pro100tak]

Код: Выделить всё

---------------------       ----------------------
|    Mailbox        |       |    Messages        |
---------------------     1 ----------------------
|    sender_id      |     ->|    message_id      |
|    receiver_id    |   /   |    (атрибуты       |
|    message_id     |<-     |      письма)       | 
|    is_read        | many  ----------------------
---------------------
 

Таким образом, устанавливая в ноль или нулл сендера или ресивера мы удаляем из одного или другого мейлбокса. Ещё решается вопрос с множественными письмами (лишняя запись в мейлбоксе) и экономится место. Для выборки писем склеивать таблицы ВООБЩЕ не рекомендуется. Сделать это можно подзапросом (как известно, селекты проходят очень быстро, особенно если ключиком перевязать, плюс сделать уникальный ключ сендер-ресивер-мессага). Подзапросы можно и не тулить в запрос один, можно задефайнить переменную мускуля под это, которая будет жить в пределах сессии БД. Идентификатор прочтения может быть один (ИМХО прочитано ли сообщение отправителем - нонсенс).
Тогда запрос на выборку полученных писем будет таким:
SET @messages_id = SELECT message_id FROM mailbox WHERE receiver_id = %receiver_id%;
SELECT * FROM messages where message_id IN (@messages_id) (доп. условия для этого селекта).

Удаление писем производится легко и просто - устанавливаем в нули сендера и ресивера в мейлбоксе, а кроном, каждую ночь просто удаляем все записи из двух таблиц.
Пагинацию организовать - тоже не проблема, даже не офсетом, а обычным лимитом и начальным индексом, таким образом уменьшится количество просматриваемых записей. Это надо гуглить.

ИМХО, такой способ хранения не предусматривает избыточности. Плюс, можно допилить таблицу мейлбокса и втулить поле парент_мессаги (или использовать путь - какбы - ид писем через разделитель) - тогда можно будет строить цепочки.

ЗЫ - пипец умаялся пока таблички нарисовал

[Vasiliy]

А каким запросам вытащить из бд данные что бы преобразовать примерно в такой вид ?
 _______________________________
|                      |  отправленных n   |                            
|sender_id = 1  |  принятых n          |
|                      | не прочитанных n |
|______________________________|
|                      |  отправленных n   |                            
|sender_id = 2  |  принятых n          |
|                      | не прочитанных n |
|______________________________|
|                      |  отправленных n   |                            
|sender_id = n  |  принятых n          |
|                      | не прочитанных n |
|______________________________|

*убрал тег php и с рисунком не парился

[bobo]

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

А потом смотреть в ящике письма от нуля и к нулям