Creating a unique chat: sql, node, socket.io?

Question

SQL

Hello!

I want to know your opinion about the organization of creating a private chat. In this case, without experience, I would like to learn information from more experienced.

Rules for private chat: There is role A and B. Only role A can start a chat with role B, otherwise there should be no chat. Chat can contain only 2 persons.

From the sql side:
1 - If role A started a chat with B, then columns will be created in the table chat2 id, user_id, second_user_id
- All correspondence will be stored in a table chat_messageswhere there are columns chat_id, user_id, message

From the node side:
1 - before creating a chat, check that only role A creates a chat with B
2 - get chat id and create/move a room with this id in socket.io

From socket side:
1 - receive all messages in a specific room and send everything to the database

. Will such a structure work? Or is there a better option?

I would be very grateful for any help!

Answer 1

There is role A and B. Only role A can start a chat with role B

Are user roles set at the account level and do they not change from chat to chat or are they determined at the time of chat creation?
That is, when creating a chat, the role of the administrator (creator) will be determined, which will be more privileged than the one to whom he writes?

Chat can only contain 2 persons

You are very optimistic in setting such a limit. In the next iteration of development, you will want to make group chat functionality. Then the presence of columns user_id, second_user_id will stand sideways for you.
It is better to add the following columns to the chat table:
id - ident. chat
date_create - creation date
id_user_creator - who created the chat
title - chat name (you can not insert it in this development iteration)
For chat participants, it is better to provide a chat_participant table:
id - ident. participant
id_chat - id. chat from the chat table
id_role - chat role (if it is defined at the time of creation, for a group chat)
id_user - chat user
id_last_read_message - id. last read message (the easiest option is how to mark as read and then determine if there are new messages)
date_include - date of joining the chat (for a group chat)
date_exclude - date of exclusion from the chat (for a group chat)
For chat messages - chat_message table :
id - message id
id_partic/id_user - message author (can be implemented both by the key from the participant table and by the user table)
date_create - message creation date
date_update - message update date (for advanced message editing functionality)
date_delete - message deletion date ( for advanced message editing functionality)
text_message - message text
If you really want to get advanced with the message editing functionality, then you'll want to keep a history of message changes in the chat_message_history table:
id is an id. history
id_next - pointer to the next history entry
id_message - id. messages
date_change - the date the message
was modified id_user/id_partic - who changed
text_message - the state of the message text

Answer 2

Well, at first glance, there are no problems, especially if you deliberately omitted the part with registering users and assigning roles to them. Well, I would add an "access" column to each entry in chat_messages with access only to the specified roles (which may include roles A and B and I would also add the "FBI" role to access the system administrator's chat), otherwise you have someone anything to the correspondence will get access. And, of course, the ability to delete messages (although I would not delete, but simply change the access rights to the record to "only FBI", as is implemented, for example, in VK and TG).
It remains the simplest and fastest - implementation.

Answer 3

And how much does it cost to create such a chat? I tried to ask the question myself but the moderator didn't allow it. I can't really find the average price online...