Chat and unread user messages, how to properly design a database?

Question

MySQL

You need to make a chat something like vkontakte messages. That is, anyone can create a conversation by inviting n numbers of people to the room, after which there is communication between them, at any time you can close the chat window, but opening it after a certain time will still see the entire message from the participants in the conversation, and for such a user and you need to output a message that he has unread messages in a specific chat (room)
I sketched a database schema, but I can’t figure out how to properly implement the ability to get unread messages for each user in the room. There was an idea to connect the user and the message through the auxiliary table read_user_mess with a flag of whether the message was read by the user, but it annoys that when creating a message, you will have to do a bunch of inserts in read_user_mess for user that are located in the room in which the message came. This option is of course acceptable if only records get there those who read the message, and not immediately everyone who is in the room, but then how to create a request to select messages not read by the user to the room

Answer 1

Inserts are not a problem, since you will still need to organize some kind of buffer in memory, for example, for reddis and update all this stuff in batches and not one at a time, and preferably in a separate thread, through ZeroMQ for example.
Actually, for this reason, you should not particularly strain yourself about the architecture of the base, in any case, you should avoid communicating with the base from the chat, since in any case it will be insanely slow.

Answer 2

If you understand everything correctly, then the
date is implied by the message.
The user can write the date of exit from the chat.
respectively unread - just a difference in date.

Answer 3

To store undelivered messages, it is more expedient to use redis and do it as follows:
If the user is not currently online, write messages to redis through rabbtimq (ZeroMQ) with special workers. Then it will be possible to smear the load and there will be no peaks.
It is also desirable to store all messages for a particular user in his "personal" list, and not in the room list, then it is possible (if necessary) upon successful receipt - to clear the cache and redis will not bloat.
If it is necessary to store the history of messages, it is advisable to write a worker that will take data from the cache every n seconds and write it to the database in a separate collection or table, and clear the cache in the same way.
When a user connects, simply make a request to receive undelivered messages from the cache, if the cache is empty - load from the database from a special table of undelivered messages.
Why do we need a separate table (collection) - since unread messages are data that are constantly in operation - this table will not constantly increase in size (it will, but much slower, because it also needs to be cleared), respectively, the selection for this table, taking into account indexing will be much faster.
Good luck :)