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

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."...