Dialog database structure: how to build?

Question

PHP

Who can help to correctly build the structure of the database of dialogs? I want any of the interlocutors to be able to "softly" delete a dialogue or message, that is, if one interlocutor deletes, then the second one has a message, and if both are deleted, then the message is permanently deleted, the same with the dialogs themselves.

Answer 1

You can make a field (for example, deleted) with a bit mask
00b - visible to everyone
01b - deleted by one person (for example, the sender of the message)
10b - deleted, respectively, the recipient of the message
11b - both deleted, you can delete the record from the database (for example, by script by cron, or immediately check the condition at the time of deletion by the user)

Answer 2

once implemented a simple exchange of private messages on one site.
got by with one table. Has been working for three years. There are about 100 thousand records at the moment.
Maybe my version will be useful to someone.
table structure:
mail_id
mail_userid_from - sender id
mail_userid_to - recipient id
mail_text - message text
mail_data - sent date
mail_status - read or not by recipient - to display number of unread messages
mail_delete_userfrom - deleted/not by sender
mail_delete_userto - deleted/not by recipient
the last two fields are just responsible for "soft deletion". By default there are zeros. If the user deleted the message, then 1 is set and the message is not shown to this user. Theoretically, if both fields have 1, then such records can be bang from the table.

Answer 3

Firstly, I would store all correspondence in one cell, and secondly, I would stupidly duplicate it. This, of course, is a waste of space, but it's simple.