A Good Database Design for E-Mail or Private Message

Question

Hi, everyone.

For now, I'm working with message system for my site. The message is used to send messages between member of the site. It can also be used to send a friendship invitation, etc. However, I haven't found a good database design for it. I want the message system to use a thread style, just like e-mail. But since I'm not an expert with complex database design, I cannot figure how to do so.

So far here is my design,

CREATE TABLE messages (
  message_id            BIGINT      PRIMARY KEY,
  message_date_time     TIMESTAMP   DEFAULT NOW(),
  message_subject       TEXT,
  message_body          TEXT,
  message_attachment    TEXT, -- path to attachment folder
  message_sender_id     INT, -- FK to table user
  message_sender_status INT, -- 0 = deleted by sender, 1=default (can be seen on sender outbox)
);

and another table...

CREATE TABLE message_recipients (
  message_id               BIGINT, -- FK to table messages
  message_recipient_id     INT,    -- FK to table user
  message_recipient_status INT,    -- 0=deleted from recipient inbox, 1=new message, 2=read
);

I believe I need another table to store the link between message, thats' why I need these

CREATE TABLE message_reply (
  message_id        BIGINT, -- FK to table messages
  message_to_reply  BIGINT, -- FK to table messages
);

But those tables only make me hard to query and handle on my PHP page. I only want the user can observe the source e-mail and the reply (like GMAIL or Facebook's Wall)...
Any better advice?

Additional description

I want the message can be sent to many recipient. But once it sent, it cannot be modified. Let's say I sent a message to X, Y, and Z. When X reply, there will be a message from X in my inbox. And if Z reply, there will be a message from Z in my inbox, with same subject with X (e.g. RE: subject of my first mail). When I choose X's message, there will be X's message, followed by my first message. If I choose to sent X a reply, X will receive a message contains my reply, her reply, and my first message. Whether I reply Z or Z reply me again, that will be another matter from X. X cannot see what Z wrote and on the other hand, Z cannot see the conversation between me and X. Complicated isn't it? That's why i on the verge of death because of these matter. Sigh -_-,

   

Thank you
Tony

Answer 1

if the message is sent by 1 person and only received by 1 person, you dont need a link-table you juse do:

message
- message_id
- recipient_id -> links to user.user_id
- sender_id -> links to user.user_id

user
- userid

if the message can be a reply to another message, just add a field to the message itself to say that it is a reply to:

message
- parent_id -> message.message_id, or null if it isn't a reply.

this is a pretty easy setup, it isn't optimal if you are going to extend it a lot but this works pretty simple.