How to implement private mail for website?

Question

Hi,

How would you go about implementing private mail functionality such like Bebo/Facebook and other social networking sites?

You have the option to post public comments on a member's profile, but you can also send a private mail.

I was considering using XML and just storing it as a field in that particular user's record. Does this sound like a bad idea?

Anyone have a better suggestion? I am unsure as to which would be the best solution.

Answer 1

I may have misunderstood what you want, but how about creating a mail table, with for example

• Sender
• Recipient
• Subject
• Message
• Sent
• Read (bool)

And then just add a row to that table when someone sends a private message to someone.

Answer 2

I would strongly recommend against storing the messages in a field on your user table. This is very likely to result in performance problems as your application grows. As another answer suggested, I'd add a table specifically to store message data. Below is a pseudocode example of what your tables might look like.

UserTable
{
    ID INT, -- a unique system generated ID
    USER_ID CHAR(20), -- also unique, this is a user provided ID
    FIRST_NAME CHAR(40),
    LAST_NAME CHAR(40),
    BIRTH_DATE DATE 
}

UserEmailTable
{
    ID INT, -- a unique system generated ID
    USER_ID CHAR(20), -- this ties the entry to the record on UserTable
    EMAIL_ADDR CHAR(128), -- user provided email
    PRIORITY INT, -- Specifies the users 0..N-th address
}

MailTable
{
    ID INT, -- a unique system generated ID
    SENDER_ID INT, -- this ties the entry to the record on UserTable
    RECIPIENT_ID INT, -- this ties the entry to the record on UserTable
    CREATE_DATE DATE,  -- record when the message was created by sender
    READ_DATE DATE,  -- record when the message was read by recipient
    PRIVATE BOOL, -- indicates if this is a private message
    MESSAGE BLOB -- the message body
}

Please keep in mind, this is just an example and it may not address the specific concerns of your application.

One final thought: Are you actually planning to store XML in field directly or using some sort of XML<-->SQL mapping tool? If you are storing the XML directly, you may not taking advantage of the abilities of your database. Your concerns about performance are valid, but a well designed & tuned database should easily be able to handle millions of records in a single table.

Hope that helps.