Private Message Database Design

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_idWHERE 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    timestampmessage_recipient    message_id    user_idmessage_status    message_status_id    message_id    user_id    is_read    read_datetime    is_deleted    deleted_datetimeSELECT 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_idWHERE     (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."...

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          |  datetimeglobal_message_tbl - g_message_id   |  int(11)  | Primary Key / Auto_Increment - g_message_type |  int(11) - sender_id      |  int(11)  | FK to sender - date           |  datetimeglobal_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.