Дизайн базы данных личных сообщений

Ответ 1

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

Вот некоторые SQL:

SELECT M.*, MTU.* 
FROM messages M 
    LEFT JOIN message_to_user MTU ON MTU.message_id=M.message_id
WHERE MTU.receiver_id={$UserID} OR M.message_type={$GlobalType}
ORDER BY M.created_on DESC

[EDIT] Проблема. Каждый пользователь должен иметь свой собственный уникальный "прочитанный" статус для глобальных электронных писем. Вероятно, вы также захотите дать им возможность "удалить" /скрыть это электронное письмо, чтобы они не смотрели на него все время. Нет никакого способа обойти это, не создавая ни строки для каждого электронного письма, как это происходит, что, вероятно, облагает налогом, чтобы сделать это много ВСТАВКИ одновременно... или еще лучше, не создавайте статус до его чтения. Таким образом, INSERTS для глобальных электронных писем будет возникать только при чтении сообщения.

messages
    message_id
    message_type
    sender_id
    timestamp

message_recipient
    message_id
    user_id

message_status
    message_status_id
    message_id
    user_id
    is_read
    read_datetime
    is_deleted
    deleted_datetime

SELECT M.*, MR.*, MS.*
FROM messages M
    LEFT JOIN message_recipient MR ON MR.message_id=M.message_id
    LEFT JOIN message_status MS ON MS.message_id=M.message_id
WHERE 
    (MS.message_status_id IS NULL OR MS.is_deleted = 0)
    (MR.user_id={$UserId} OR M.message_type={$GlobalType})
ORDER BY M.timestamp DESC

[EDIT] Следует ли использовать message_type в качестве таблицы БД или просто как настройки в вашем коде, отчасти является личным предпочтением и отчасти вашим потребностям. Если вам нужно запросить БД и увидеть текст "личный" и "глобальный" непосредственно из вашего запроса, то вы хотите использовать таблицу message_type. Однако, если вам нужен только "тип" для обработки вашей бизнес-логики, но не нужно видеть его в результатах запроса, я бы пошел с подходом "Enum". Перечисления - это вещь С#... в PHP, ближайший у вас есть класс с константами... что-то вроде:

class MessageTypes {
    public const Global   = 0;
    public const Personal = 1;
}

Итак, ваш запрос будет выглядеть следующим образом: WHERE ... message_type=".MessageTypes::Global."...

Ответ 2

Один метод может состоять в том, чтобы отделить глобальные сообщения от личных сообщений, как я думаю, вы уже пытались сделать.

Чтобы эффективно получить статус чтения для глобального сообщения, вам нужно будет добавить таблицу с составным ключом, содержащим global_message_id и user_id вместе.

messages_tbl
 - message_id    |  int(11)  | Primary Key / Auto_Increment
 - message_type  |  int(11)
 - sender_id     |  int(11)  | FK to sender
 - receiver_id   |  int(11)  | FK to receiver
 - status        |  int(1)   | 0/1 for Unread / Read
 - message       |  text
 - date          |  datetime

global_message_tbl
 - g_message_id   |  int(11)  | Primary Key / Auto_Increment
 - g_message_type |  int(11)
 - sender_id      |  int(11)  | FK to sender
 - date           |  datetime

global_readstatus_tbl
 - user_id       |  int(11)   | Primary Key
 - g_message_id  |  int(11)   | Primary Key
 - date          |  datetime

Альтернативно объединяйте messages_tbl и global_message_tbl, чтобы каждый пользователь отправлял глобальное сообщение лично в цикле. Это уменьшает вашу схему вплоть до одной таблицы.

messages_tbl
 - message_id    |  int(11)     | Primary Key / Auto_Increment
 - sender_id     |  int(11)     | FK to sender
 - receiver_id   |  int(11)     | FK to receiver
 - status        |  int(1)      | 0/1 for Unread / Read
 - message_type  |  varchar(8)  | Personal / Global / Company
 - message       |  text
 - date          |  datetime
 - type          |  varchar(8)  

Если вы хотите улучшить нормализацию своей таблицы и упростить добавление типов сообщений в будущем, снова переместите message_type в свою собственную таблицу и сделайте message_type FK message_type_id

message_type_tbl
 - message_type_id  |  int(11)    | Primary Key / Auto_Increment
 - message_type     |  varchar(8) | Personal / Global / Company

Обновление - Пример таблицы (1 таблица)

message_tbl

message_id | message_type | sender_id | receiver_id |  status  |  message  |      datetime
    1      |   personal   |     2     |      3      |   read   |  foobar   |  12/04/11 00:09:00
    2      |   personal   |     2     |      4      |  unread  |  foobar   |  12/04/11 00:09:00
    3      |   personal   |     3     |      2      |  unread  |  barfoo   |  12/04/11 02:05:00
    4      |   global     |     1     |      2      |  unread  |  gmessage |  13/04/11 17:05:00
    5      |   global     |     1     |      3      |  unread  |  gmessage |  13/04/11 17:05:00
    6      |   global     |     1     |      4      |   read   |  gmessage |  13/04/11 17:05:00

user_tbl

 user_id  |  name
    1     |  Admin
    2     |  johnsmith
    3     |  mjordan
    4     |  spippen

Вышеприведенное предполагает, что пользователи 2, 3 и 4 являются пользователями общего доступа, отправляющими сообщения друг другу, пользователь 1 является учетной записью администратора, которая будет использоваться для отправки глобальных сообщений (доставляемых непосредственно каждому пользователю отдельно), позволяя вам видеть ту же информацию как будто это личное сообщение.

Чтобы отправить глобальное сообщение в этом формате, вы просто прокрутите таблицу пользователей, чтобы получить весь идентификатор, из которого вы хотите отправить глобальное сообщение, а затем просто INSERT строки для каждого пользователя в messages_tbl.

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