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?