5

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?

shabby
  • 3,002
  • 3
  • 39
  • 59
  • I worked on the answer to [this][1] question and its the best [1]: http://stackoverflow.com/questions/6541302/thread-messaging-system-database-schema-design – shabby Nov 01 '12 at 06:58

1 Answers1

7

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).

No'am Newman
  • 6,395
  • 5
  • 38
  • 50
  • How far have you got on your own? – No'am Newman Oct 31 '12 at 17:02
  • I was having problem in how to add recipients to every mail/message, then i found out that the recipient would not be of every mail, it would be associated with thread, thanks for the help – shabby Nov 01 '12 at 07:00
  • @No'amNewman What if the message would be coming from two types of people, employee and customers? – – Smith Jul 25 '18 at 06:48
  • @Smith: the source shouldn't matter too much. Note that my schema has a field 'from_id', which is a foreign key to the 'users' table. From this field, one can access attributes of the user. – No'am Newman Jul 25 '18 at 14:10
  • yes, but that is a FK to the users table not another table – Smith Jul 25 '18 at 20:39
  • @Smith: so one adds another field which denotes whether the 'from_id' relates to the users table or to the customers table. – No'am Newman Jul 26 '18 at 04:19