20

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
Harsha M V
  • 54,075
  • 125
  • 354
  • 529

3 Answers3

9

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.

Tim Joyce
  • 4,487
  • 5
  • 34
  • 50
  • if i delete lets say users 1 message. how will user 2 know the message is from user 1 and not from user 3 or 4 ? – Harsha M V Nov 12 '11 at 17:10
  • i updated the answer. So you would just store in the message table that `user_id` is the id of the user that created the message. for example if message->user_id = 1 and in messageUsers you delete user 1's ability to read that message, user 2 will still see the message and who created it. – Tim Joyce Nov 12 '11 at 17:13
  • This is also assuming that you are creating a relational database... hope you are. – Tim Joyce Nov 12 '11 at 17:17
3

At first I thought that when one person deleted it you could just turn either To or From to null but that would make you lose who sent the message or to whom it was addressed.

You should just add a field deleted_by which will contain the id of the person who deleted it or will be null. So when selecting records from the inbox you have something like:

Select * From Messages where to_id = MyID and deleted_by <> MyID

when you delete the message you check if the deleted_by is null, if it is you update the deleted_by field with MyID, if it is not (means that the other party deleted it as well) you delete the record.

If you want to have the same functionality for threads instead of messages (i.e. manage the conversation and not one message at a time) you should have another table (MessageThreads) in which you have the from_id, to_id fields, deleted_by along with a thread_id field. in the Messages table you subsitute the from_id to_id and deleted_by with the thread_id.

John
  • 634
  • 8
  • 17
1

There will be two tables. nodes node_user

In nodes table,

  • node_id
  • title
  • message
  • timestamp

In node_user table,

  • node_user_id(PK)
  • node_id
  • parent_node_id(for threaded)
  • from_id
  • to_id
  • timestamp
  • read

When user A send a message to user B, firstly store the message in nodes table. And then, add two records in node_user table. When user A delete the message, only delete the first record in node_user table. When user B delete the message, you can delete records from both nodes and node_user table.

Threaded Message,

  • Use parent_node_id
Thein Hla Maw
  • 685
  • 1
  • 9
  • 28
  • why do i need to store the message three times ? – Harsha M V Nov 12 '11 at 17:45
  • sorry. I forgot to delete the message column in node_user table. I will edit. You don't need to store message three times. Just one time in node table only. Use node_user table to store sender and recipient. It will also work if you add CC and BCC features later. – Thein Hla Maw Nov 12 '11 at 17:57
  • i have modified my question... to clearly state my needs. guess calling an inbox was not appropriate. Thanks a lot – Harsha M V Nov 12 '11 at 18:04
  • 1
    corrected the wrong table name "only delete the first record in node_user table." to remove the conversation from User A to User B. User B still have access the conversation – Thein Hla Maw Nov 12 '11 at 18:11
  • i guess even timestamp can be omitted and also we will need an author Id for the first table – Harsha M V Nov 12 '11 at 19:13