How to build a database for private messages?

Question

MySQL

Good evening!
I connected XMPP with HTTP and now I'm stuck on the question - how to organize data in the database to store the history of user correspondence with minimal server load under MySql, with the expectation of a large data flow in perspective, in order to avoid rebuilding the project in the future.
I really hope that here I will be prompted in the right direction. Thanks

Answer 1

I'll leave it here habrahabr.ru/post/66151

Answer 2

I know that many loaded services just do data denormalization (not only for chats, but also for news, etc.). As a rule, each message sent in a general chat (or one-on-one) gets simultaneously to each chat participant in his personal feed / table / flat file. This solves the load problems (each user reads only his own set of individual files/tables/rows and does not load the general data stores, where there can be billions of records when solving the problem "on the forehead"). It also solves the problem with deleting messages (one can delete the correspondence, and the other can keep it as a keepsake). And it also automatically solves the problem of visibility of messages in the general chat - if a person has just been invited to a conversation, then he does not see the previous messages of the participants. For getting messages in the "private zone"
Yes, some operations will become more complicated, but how often do you need the number of private messages sent by the user, or some statistics on the personal correspondence of all users, in order to make the entire messaging system slow for the sake of a small gain in access speed to this data? For all such rarely needed queries, you can attach a separate search indexer and counters, map / reduce algorithms, etc.
Imagine that you are sending copies of wedding invitations to friends and family. Each copy is a separate entity. And if one invitation breaks, then for the rest it should not break automatically. Someone wants to write something on it, for example, a plumber's phone number. Someone will crush and put it under the leg of a swinging table. Someone will make a photocopy and invite someone else to your wedding without your knowledge. At the same time, these will be different invitations, with the same content. The same goes for messages and chats.
By the way, it is not necessary to store the system of personal messages in the database. It is possible, but not necessary. Even stupid xml'y/json'y will approach for this purpose. After all, what does the interlocutor most often need from a chat? To 1) send a message to be read; 2) receive a message; 3) look into the chat history. This fits perfectly with the "each user - a separate copy of each chat" model. Yes, denormalization, but read the example above about invitations and a copier. In your case, the controller will make "photocopies" of the letter and send them to all recipients. And what to do next with this letter is everyone's business.

Answer 3

So you write how you yourself came up with and what you doubt. And it turns out not a site of advice and help, but a site for solving other people's problems on a turnkey basis.

Answer 4

The main question is what are the planned characteristics of the system? How many users, how many messages, what kind of read-write load, how much messages need to be stored, what are the requirements for fault tolerance, finally, what development resources are available, etc.
And based on this, you can give advice. I suspect that the main advice will be the question - why do you need mysql? There are many ways to organize the mechanics and storage of a chat more efficiently: redis (it has a publish / subscribe mechanism that is convenient for organizing chats), mongodb (you can also make pubsub through a tailable cursor), riak (scales great) and so on.