Designing a messaging system (database schemas)

Question

Database design

On a new project, I am making a system of internal messages between users.
Users write messages to each other and see several directories in their personal account - incoming, outgoing, trash, blacklist.
I am designing a database schema for this module.

At first glance, everything is clear, it looks something like this:
Who, to whom, subject, message, in the basket?
(there are other fields, such as viewed or new, but this is not the point)

But one message applies to two users, both see it either in their inbox, or in outbox, or in the basket, so I’ll design it like this:

Who, to, subject, message, from_trash, to_trash.

Everything seems to be OK, but now the question arises, how to empty the trash.
It is impossible to delete the message completely, as it will be deleted for both.

At first it started like this:
...., from_trash, to_trash, from_del, to_del

But this is generally ugly, a cloud of flags ...

I think it can make a field like status:
1 - both see it,
2 - the author in the basket, the addressee no
3 - both have
4 in the basket - the author deleted it at all, the addressee has it in the basket
And so on, there are a lot of options, damn it, it’s also not beautiful, which means it’s not an option.

So the question is - how to properly design a data schema for this task?
And another question - maybe somewhere there are patterns for such things, so as not to reinvent the wheel.

Answer 1

I see two solutions: simple and correct.
Simple is to create a message for the sender and recipient. Then they become initially independent.
The correct one is what you almost came to: make a folder table, a message table and a link table: message-folder-user.
folder table: id|name
message table: id|date|title|text and so on
link: id|userId|folder|state|messageId + I would add a unique index on userId|messageId to ensure the integrity
state — message state for the user: new , read, in the trash, deleted for the very
only nuance here - the selection for the trash folder should be done by status and not by the folder table
if the user can have their own folders, then the structure should be slightly different: the folder table will refer to the user, and userId should be excluded from the bundle.

Answer 2

Here, it may be useful to someone who comes here from Google for example: eax.me/database-design

Answer 3

While I tend to such scheme:
table message:
id | subject | body |… other… | user_id
- here user_id is the sender, it is always the same, in contrast to the recipients
table message_user_rel (message relationship with users)
id | message_id | type (incoming, outgoing) | trash (basket)
But here is also a problem:
If the message deletes the recipient, then when selecting outgoing messages for the sender, it is impossible to determine who the recipient is (message deleted
)
again, an excess of all these flags is obtained, and an excess of data in the database (in fact, you can’t delete records, but only mark them)