How to design MYSQL for chat node.js?

Hi all.
You need to make an adequate chat on node.js as part of the application which will his buns inherent in any normal chat. On this basis arose a couple of questions:

1) to Store the chat history in the form [sender] [recipient] [message] [date and time] [had read?] in MySQL BD is it advisable? If the chat will be very popular, not will this create too high load on the database? And what about the number of records which will continue to grow with great speed? They need as is also to parse at times to download a history of previous messages between users?

If there was a possible correspondence only between 2 people, then the previous version of data storage in a DB would be fine, as the user id that sent the message and the user id from which it received + the reading from the end of the database that would be even sorted by time can be easily obtained history of 2 people.

2) But if one were to add the ability to add people to a simple chat from which it automatically should become a group? As it is now better to store the messages from the chat in the database if recipients can now be a lot? And how easy to get a story from this dynamically created group of participants?

And the last question. There's kind of easier but still not completely clear.

3) In the application already, there are groups in which the user can enter. The group is just a string in the DB in the groups table which has id, name and other parameters. Need to implement a common chat group. I imagine it so: in each group, the database will store the array aydishnikov users who are in this group as a string. I am concerned about the size of this string with the popularity of the group from what it makes me think about what I'm doing wrong. How to do it properly? In addition, if I store the list of group members I won't be able to execute SELECT query for a particular user to get an array of groups in which it consists.
July 4th 19 at 23:53
1 answer
July 4th 19 at 23:55
You are wrong to consider the system chat.
In chat rooms there are rooms, inside the rooms, messages and participants.
Tete-a-tete is a private room with 2-mi participants.

- id
- name
- private

participant (party chat)
- id
- name

roster (list of participants in the chat)
- room_id
- participant_id

history (message history)
- id
- room_id
- participant_id
- type (type of message, sometimes you need the system messages from bots)
- text
- deleted

And now forward - learning JOINS you!

On the load - with proper design of the application, the load on the database will be minimal, mostly checking who belongs to a room and recording a story.
Any significant load will be under 10K people online. Believe me, this is a very large attendance.
With a larger attendance your problem will be solved by horizontal scaling (sharding the database).

Find more questions by tags AndroidJavaScriptMySQLNode.js