Suppose we create a website with the ability to exchange messages between registered users. How best to organize the storage of such messages?

I give all the options that I thought about (most likely, all of them are not suitable for practical use, but maybe you will have something to add to what I wrote).

  • Storage of messages in the general database table, in which there are Boolean fields "Display at sender" and "Display at recipient" for the implementation of the function of deleting letters (when both fields are false, the message is deleted from the table). The disadvantage of this approach is a high level of confusion and a growing time for searching in the table as the users and their messages increase. Dignity - there is no duplication of messages to in the following options.
  • When adding a new user, create a table with messages for each user. Dignity - faster search than the first option, as well as logical separation. Disadvantages - duplication of messages in two databases, difficulties with the organization of table names in the database, as well as the rapid growth of memory with each new addition of tables.
  • Create a new database for each user, where there will be Inbox, Outgoing and many others tables not related specifically to this task. Dignity - the maximum level of organization of data by type "one user - one database" with all its data. The disadvantage is the rapid growth of memory occupied with each new database.
  • Store message in files. There are no comments here.

If in general it is difficult to speak about the database and their processing, then in this question we will by default use MySQL and PHP for processing.

  • Недостаток такого подхода - высокий уровень путаницы и возрастающее по мере увеличения пользователей и их сообщений время поиска по таблице. - Will you manually search for messages there? The algorithm is completely indifferent to how many users and how many messages are there. To speed up the search and selection, you need to create indexes in the fields of the conditions. - Visman
  • Does your comment mean that the first option is appropriate? - Bokov Gleb
  • 2
    Your question does not have a clear answer. It can be closed with the wording необходимо переформулировать вопрос так, чтобы можно было дать объективно верный ответ . But I believe that for all personal messages, it is enough to have 1 table in which the fields will be: message, message date, sender, recipient, sender flag, recipient flag. - Visman
  • As for the “does not have a definite answer”, I do not agree, because if you bring a set of pros and cons with argumentation for each item, it will be fairly objective. Another thing is whether there is someone who will generously kill his precious time for this ... - Bokov Gleb
  • @Visman, would make a comment as an answer. He is faithful and unambiguous. All the rest that the author suggested is the delirium of a madman. - ArchDemon

0