Handle to chat room with mysql database
-
Delete the recipient-table, and add a table like below; DeletedMessagesPerUser UserId MessageId Add a record to that table when the user "deletes" a message. When fetching all messages for a particular user, fetch all messages where the messageId isn't named in the DeletedMessagesPerUser table.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Thats a good idea. Please if i want to track unread messages per a user, it means i would have to create another table for the unread messages,right? or there is a better way to deal with it
-
Thats a good idea. Please if i want to track unread messages per a user, it means i would have to create another table for the unread messages,right? or there is a better way to deal with it
Member 10949986 wrote:
it means i would have to create another table for the unread messages,right?
Yup - a table with both a reference to the user, and a reference to the messages. Any message-id that's not in the table for that user, would be considered 'unread'.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Member 10949986 wrote:
it means i would have to create another table for the unread messages,right?
Yup - a table with both a reference to the user, and a reference to the messages. Any message-id that's not in the table for that user, would be considered 'unread'.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Don't you think this would give my queries more load using four tables and doing many JOINs. Cant we have a simple way to solve this, which will increase speed. That even means, at every insert into the message table, there should be the same insert into the unread table, when all members are online
-
Don't you think this would give my queries more load using four tables and doing many JOINs. Cant we have a simple way to solve this, which will increase speed. That even means, at every insert into the message table, there should be the same insert into the unread table, when all members are online
Databases are optimized to do that stuff :) Look into partitioning your table if you need more speed. If that's not enough, then you'd more be thinking about client-side processing. Do you need to "know" on the server whether a user read one of the messages? If not, then you might be helped quickly with a XMPP-server. The local client could then keep track of the messages it receives, and which ones are read.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Databases are optimized to do that stuff :) Look into partitioning your table if you need more speed. If that's not enough, then you'd more be thinking about client-side processing. Do you need to "know" on the server whether a user read one of the messages? If not, then you might be helped quickly with a XMPP-server. The local client could then keep track of the messages it receives, and which ones are read.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Eddy Vluggen wrote:
Do you need to "know" on the server whether a user read one of the messages?
Yes please.
-
Eddy Vluggen wrote:
Do you need to "know" on the server whether a user read one of the messages?
Yes please.
-
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 priviledge 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 wouldnt 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 am to 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! Please is there a way to handle this, scalability and performance wise? Any idea is appreciated. Thanks.
Delete the recipient-table, and add a table like below; MessageStatusPerUser UserId MessageId IsDeleted Add a record to that table when the user read a message set 'IsDeleted' to false. When user delete a message, set IsDeleted to true. When fetching all messages for a particular user, fetch all messages where the messageId isn't named in the MessageStatusPerUser table and Messages marked 'IsDeleted' as false in summary: messages not appearing in MessageStatusPerUser table are unread. messages appearing in MessageStatusPerUser table and flagged IsDeleted as false are read. messages appearing in MessageStatusPerUser table and flagged IsDeleted as true are deleted messages.
-
Delete the recipient-table, and add a table like below; MessageStatusPerUser UserId MessageId IsDeleted Add a record to that table when the user read a message set 'IsDeleted' to false. When user delete a message, set IsDeleted to true. When fetching all messages for a particular user, fetch all messages where the messageId isn't named in the MessageStatusPerUser table and Messages marked 'IsDeleted' as false in summary: messages not appearing in MessageStatusPerUser table are unread. messages appearing in MessageStatusPerUser table and flagged IsDeleted as false are read. messages appearing in MessageStatusPerUser table and flagged IsDeleted as true are deleted messages.
Alright, thanks soo much...If I understood it well, this means that a message is inserted into the MessageStatusPerUser only when the user has come online to read the message.That is it, right?
-
Alright, thanks soo much...If I understood it well, this means that a message is inserted into the MessageStatusPerUser only when the user has come online to read the message.That is it, right?
yes, only when you consider the message is read by user.
-
yes, only when you consider the message is read by user.
Alright... thanks buddy for your time.