Private Message Database Design

Question

Alright, so I think I'm pretty close to having what I need, but I'm unsure about a couple of things:

TABLE messages

message_id
message_type
sender_id
timestamp

TABLE message_type

message_type_code (1, 2, 3)
name (global, company, personal)

TABLE message_to_user

message_id
receiver_id
status (read/unread)

Goals:

1. Be able to send GLOBAL messages to all users.
2. Send PERSONAL messages between 1 or more users.
3. Determine if any of these messages have been read or not by the the receiver.

Questions:

1. Does my schema take care of all that it needs to?
2. What would a sample SQL query look like to populate someones inbox, bringing in GLOBAL messages as well as PERSONAL messages - I'd like to be able to determine which is which for the UI.

And please feel free to add to my schema if you feel it would benefit.

Answer 1

The one method can be to separate the global messages from the personal messages as I think you have tried to do already.

To effectively get a read status for a global message, you would need to add a table with a composite key containing the global_message_id and user_id together.

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

Alternatively merge the messages_tbl and global_message_tbl so they each user is sent a global message personally in a loop. This reduces your schema right down to one table.

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)  

If you want the ability to normalise your table a bit better, and make it easier to add message types in the future, move message_type back into its own table again, and make message_type a FK of the message_type_id

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

Update - Sample Table (1 Table)

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

The above assumes users 2, 3 and 4 are general users sending messages to each other, user 1 is the admin account that will be used to send global messages (delivered directly to each user individually) allowing you to see the same information as if it were a personal message.

To send a global message in this format you would simply loop over the users table to obtain all the ID's you want to send the global message out to, then simply INSERT the rows for each user in the messages_tbl.

If you don't anticipate your users sending millions of messages a day as well as regular global messages to millions of users then the number of rows shouldn't be an issue. You can always purge old read messages from users by creating a cleanup script.

Answer 2

Schema looks like it will work. Should probably have a Created date too. There's no way to know if you've read a global message though without creating entries for everyone.

Here's some 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] Problem: Every user needs to have their own unique "read" status for global e-mails. You probably also want to give them the ability to "delete"/hide this e-mail so they don't have to be looking at it all the time. There is no way around this without creating either a row for each e-mail as it's going out, which is probably taxing to do that many INSERTS all at once...or better yet, don't create a status until it's read. This way, INSERTS for global e-mails will only occur when the message is read.

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] Whether to use message_type as a DB table or simply as settings within your code is partly a personal preference and partly your needs. If you need to query the DB and see the text "personal" and "global" directly from your query, then you want to use the message_type table. However, if you only need the "type" to handle your business logic, but don't need to see it in query results, then I would go with an "Enum" style approach. Enums are a C# thing...in PHP, the closest you've got is a class with constants...something like:

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

So, your query would be: WHERE ... message_type=".MessageTypes::Global."...