The following tables should be created in the chat room:
- a table with the list of chats
- a table with the list of chat participants
- a table with a list of messages
- a table with the statuses of messages.
How the chat should work in general. You have users who can chat. A chat room can consist of several groups (the chat table), essentially separate chats in each of which users can chat (the party table). Messages of each chat are added (messages table) from chat participants to whom it is possible to show notifications when new messages are added and to check which messages the user has already read (message_status table).
I assume you already have a table users, which stores the users.
A table with a list of chat groups
The table with the list of chat groups will store the data of the chat groups. The table looks like this:
| Name | Description |
| chat_id | serial id of the chat |
| name | chat title, name |
| user_id | id of the user who created the chat |
The table with the list of chat group members
The chat group list table will store the relationship between the chat group and the users who are participating in the chat group conversation. The table looks like this:
| Name | Description |
| chat_id | chat group id |
| user_id | The id of the user who participates in the chat correspondence |
Table with the list of messages
The table with the list of messages will store the messages that users write to each other in chat. The table looks like this:
| Name | Description |
| message_id | ordinal message id |
| chat_id | chat id |
| user_id | The id of the user who added the message |
| contect | message content |
| date_create | message date |
Table with message statuses
The table with message statuses will store notification statuses that determine whether a certain user has viewed a new added message or not. The table looks as follows:
| Name | Description |
| message_id | message id |
| user_id | user id |
| is_read | whether or not the message has been read |
