Database design for email-style application

Question

I'm looking for some advice on how to design a database for an email-style application, specifically how to handle sending a message to multiple users, and displaying what messages were sent to what users.

This is what I have so far:

Messages (Primary Key is Id)

Id (Identity)
SenderId (Foreign Key to Users.Id)
<message data>

ReceivedMessages (Primary key is MessageId + RecipientId)

MessageId (Foreign Key to Messages.Id)
RecipientId (Foreign Key to Users.Id)
IsRead

So for every message sent, there would be one row in Messages, with the data, and then one row for each recipient in ReceivedMessages.

But what if I want to view all the messages sent by a user, and who they were sent to? For each message, I'd need to find all the ReceivedMessages rows for that message, and join all of those with the user table, and then somehow concat all the names (something like this: Concatenate many rows into a single text string?). Might this cause scaling issues, or is it not really anything to worry about?

Any thoughts/suggestions? Thanks.

score 2 · Accepted Answer · answered May 16 '11 at 20:26

I see no problem with your design, and would not anticipate and scalability issues with proper indexing on your tables (unless you are talking about massive scale, e.g. gmail, Yahoo mail, etc.).

As far as concatenating the recipient names, I would recommend you do this on the application side and not in SQL, or determine whether you need to do it at all (you might want to show a list and not a concatenated string).

score 1 · Answer 2 · answered May 16 '11 at 20:26

all the messages sent by a user, and who they were sent to

You can do it as an aggregate query, something like:

SELECT u1.user_name, m.message, GROUP_CONCAT(DISTINCT u2.user_name)
FROM messages m JOIN users u1 ON (m.senderID=u1.user_id)
                JOIN receivedmessages r ON (m.id=r.messageId)
                JOIN users u2 ON (r.RecipientId=u2.user_id)
GROUP BY u1.user_name, m.message;

But because Recipients is essentially unlimited, you may run up against the string length limit on GROUP_CONCAT.

So it's likely better to do an unaggregated select and process the records for display in your application layer:

SELECT u1.user_name, m.message, DISTINCT u2.user_name
FROM messages m JOIN users u1 ON (m.senderID=u1.user_id)
                JOIN receivedmessages r ON (m.id=r.messageId)
                JOIN users u2 ON (r.RecipientId=u2.user_id)
ORDER BY u1.user_name, m.sent_date, u2.user_name;

score 0 · Answer 3 · answered May 17 '11 at 04:22

Users change name and email, but not their login. Consider mimicking what happens in a unix-based mailbox (e.g. pine) instead:

received_messages (
  user_id,
  message_id,
  message_date,
  message_title,
  message_content,
  message_sender,
  message_recipient,
  message_is_read
)

and sent_messages along the same lines, i.e. two "files" per user.

Or even merging the latter two with a sent/received flag.

score 0 · Answer 4 · edited Nov 10 '12 at 08:18

I am facing the same challenge of creating an email or messaging system for a website... You guys forget one thing... IsRead, IsDraft, IsFlagged, IsReply, IsTrash, etc... need to be in a separate table since the same message will be flagged, read or unread by two or more people!! So, we must have a Status table as shown below...

StatusID    int     
MessageID   int  
MemberID    int  
DateTime    datetime    
IPAddress   varchar(65)  
IsRead  char(1)   
IsDraft char(1)  
IsFlagged   char(1)   
IsForwarded char(1)  
IsReply char(1)  
IsTrash char(1)

You will need at least three tables besides the member or user table:

mail  
folders  
status  
attachment  
log

If this is for an existing website... I would separate the mail system into a separate database if you expect this mail system to have a lot of activity.

Database design for email-style application

4 Answers4