What does a proper chat architecture look like?

Question

MySQL

Good day!
I am actually creating a chat and the question arose before me:
What does the most correct data storage architecture for a chat look like?
There is already a users table, which contains email, nickname, login data, etc.
Is it worth making, for example, a separate table with messages and a separate table for linking a message to a user (3 tables in total)?
It seems that then some duplicated data will be able to disappear, but when pulling out data, you will need to use Left Join, which is quite difficult for the database (as far as I understand).
In general, how to store chat data correctly?

Answer 1

it will be necessary to use Left Join, which is quite difficult for the database (as far as I understand)

There is nothing heavy in LEFT JOIN for the database. Don't be afraid of JOINs - this is an absolutely standard mechanism.
What for to you in this case the additional table for communication? You can make a "user_id" column in the posts table.