How to properly store private messages of conversations/dialogs in MySQL using node.js?

Question

MySQL

Good day! A chat was made on node JS using socket.io. The database is used by MySQL. How to properly store correspondence and dialogues:
1. Between users
2. General user dialogues (rooms with closed dialogues)
3. (Most important!): General chat.

Answer 1

I would make three tables:
1. Dialogues Conversations(id, name, type, status), where type - open chat, closed chat, general chat, status - the dialog is open, the dialog is completed, the dialog is archived.
2. Participants Conversation_Users(conversation_id, user_id) - (conversation_id, user_id) - unique.
3. Chat messages Conversation_Messages (id, conversation_id, user_id, send_date, message).
If the dialog is closed and more than a certain time has passed (month, year), then all related data can be serialized and thrown into a file or archive table, and unnecessary data can be deleted.

Answer 2

I'll just write some of my thoughts.
In my opinion, in terms of storing information, there should be no difference between a dialogue between two or three or more users. The only question is the number of participating parties (n>1).
Today, it is customary to encrypt user data. It seems to me that the architecture of the database can be very dependent on how important it is to protect your users' data. It is necessary to approach this issue thoroughly and in no case use self-made algorithms.
You can protect not only correspondence data, including media, but also keep participants anonymous. Member IDs (hash, not primary ID) can be mapped to user IDs.