Handling a chat room in a database

database-designperformance

I have been searching all day to find a better answer to my question but those I found was useful
but do not critically analyse my question.

I am building a chat room using php, mysql, jquery and ajax.
The target group members are 3000 people who will frequently
chat every day, so I am expecting like one million messages a day.
The chat room is open to all the members, that means
every member has the same privilege to send and view
sent messages.

In this case, every member has the permission to delete messages whether sent by him or different members,
however deleted messages should not affect other users. I wouldn't also keep messages for more than two days,
meaning every sent message should be deleted after two days.

Below are the sample tables that represents the logics above

users table

|  u_id | 
|  name |  


messages table

| msgID            |  
| msgBODY          |
| msgTime          |
| senderID--FK     |
| deleted_by_sender|


recipient table

| recID               |
| msgID--FK           |
| recipientID ---FK   |
| deleted_by_recipient|

Now, if I implement the schema above, it means that every single sent message has to do 3000 inserts into the
recipient table and one insert into the messages table. This also means that if there are 50 sent messages within 1 minute there would be more
inserts within the one minute. At the same time 3000 people are viewing the messages. Awwsh! more work load on the database within that minute.
hmm!

Is there a way to handle this, scalability and performance wise?

Best Answer

The following is kind of a shot in the dark in lack of clarification of the question, but as I had built it already earlier I'll post it in absense of any other answers.

How about implementing the message-deletion-per-recipient so that you only keep track of those messages that have been tagged as deleted-by-recipient? That would translate into getting rid of the recipient table as you had it and instead having a recipient table plus a cross-ref table for deleted messages:

recipient table

| recID              |
| recipientID --FK   |


deletedmessages table

| msgID --FK         |
| userID --FK   |

When fetching messages to be displayed to a given recipient, it would be enough to either join the two tables on msgID or to do something like

... WHERE NOT EXISTS (SELECT 1 FROM deletedmessages dm WHERE dm.recipientID = <current_user_ID> AND dm.msgID = m.msgID)

to filter out the deleted ones. Having deletedmessages indexed by both columns (probably mandatory to get the foreign keys references properly set up) would allow the DB to execute this very efficiently.

This way deletedmessages would only grow when a user actually deletes a message and not grow that much.

There's a SQLfiddle with the above solution.

Related Question