Making a simple email/messaging database schema

Question

I am creating a small mail/message sending database schema. It is not actuall mail system, but sort of a messaging portal withing an organization, with the following use cases:

1. employees can brodcast messages to all employees
2. employees can send messages to eachother
3. employees can reply to the recived messages
4. employees can view the mails which he/she has replied to by seeing a replied at "timestamp" column in the inbox (i.e. there has to be a 'repliedat' column in the inbox which should show the timestamp when this mail was replied, it should be empty if it has not)
5. When he opens a particular message he sees a threaded view of the messages, latest being on the top.

My Question How do I track if a particular message is a reply to any message, in emails, i believe it is embedded in the header. Do I keep a 'repliedto' id field which points to another message of which it is the reply?

How do I create threads, or chains?

Answer 1

Keep a field in the database table which points to the original email. Each email can only have one predecessor (or 'father') whereas any email can have none or more successors (or 'sons'). Thus it's best to maintain a record of what the predecessor was.

This is also how you create threads or chains: the first message in the thread will have its 'father' field equal 0, but later messages will contain other values in this field. It would also help if you maintain a field called 'thread' or 'conversation', making it easy to know which messages are connected to which threads.

The interesting part is not necessarily how you store the links in the chain but how you traverse the chain.

Following is a simple data schema

USERS
id - autoinc
name - varchar

THREADS
id - autoinc
name - varchar

STATUSES
id - autoinc
name - varchar

MESSAGES
msg_id - autoinc
from_id (fk to users table) - int
datesent - date
thread (fk to threads table) - int
father (pointer to previous message in this thread) - int
text - varchar

RECIPIENTS
id - autoinc
msg_id (fk to messages table) - int
to_id (fk to users table) - int
status (fk to statuses table) - int 
dateread - date

As one message can be sent to many people, there exists a one-to-many relationship between the MESSAGE and the RECIPIENTS. Each recipient would read the message at a different time and would have a different status (unread, read, deleted, deleted unread, etc).