Database Design for storing Chat Messages between people

Harsha M V picture Harsha M V · Nov 12, 2011 · Viewed 21.6k times · Source

I am trying to build a messaging/chat system. which can store conversation between two people in a chronological order. Also if User A deletes the conversation User B still should have access the conversation until he wishes to delete them.

  1. Inbox - All the messages recieved by the user from various users will be displayed with the latest message from that particular thread.

  2. Conversation Screen - Chronological order of the conversation between the User A and User B

This is the basic structure of the database i have come up with. Should i store the messages twice in the database ?

  1. id
  2. to_id
  3. from_id
  4. message
  5. timestamp
  6. read

Answer

Tim Joyce picture Tim Joyce · Nov 12, 2011

I would use a lookup table for the messages that would store who has the rights to view that message

table->message                   |    table->messageUsers
id->0, message->'hi', user_id->1      user_id->1, message_id->0
                                      user_id->2, message_id->0

That way if a user deletes their message they are actually just deleting their relationship to the message not the message itself. you just remove them from the messageUsers table. or set a active field to 1 or 0.