Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Could you please clarify the recipient table? If recID is the PK, what is recipientID used for? Also, what links a user and a recipient? (or is recipientID actually u_id?) –  zagrimsan Jul 17 at 9:03

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.