How to create a dialogue structure?

Question

MySQL

Hello, I just can't think of a good structure for exchanging dialogues on the site, taking into account the conditions:
1. A user can communicate face-to-face with another user.
2. Can communicate with a group of users (conversation)
The messaging system itself is an analogue of the dialogue system of the social network VKontakte.
For example, there are tables:
user
id, email ...
Dialog
id, name
Message
Id, sender_id, dialog_id, text
UserDialog
user_id, dialog_id
Here is further my head does not cook - how to check the presence of dialogs between users. I assume that we first create a message and select a recipient, then we check if there is such a dialogue between users before. If it is, then we get the id of the dialog, write it to the message table and save it. If not, then we create.
I would be very grateful for a detailed answer.

Answer 1

IMHO you are seriously multiplying entities on a simple task. I think 2 tables are enough to store everything.
1) the actual messages
id | sender_id | receiver_id | chatroom_id | message | datetime | readed
2) chatroom (fields to choose from)
and logic to steer what it is.
If there is a sender and a receiver but no chatroomid - a personal dialogue,
If there is no receiver and there is a chatroom - a general chat,
If there are all 3 - a general chat with a personal appeal, just like in VK.
UPD: if there is a need for access levels to chats, then an additional table with a restriction policy and user groups will naturally be needed, but this, IMHO, is a little beyond the scope of the original task.