mysql - 连接用户与消息的数据库设计困境

Question

这是我想出的，但我不确定其中哪一个是“最好的”。也许还有另一种我可能不知道的更好的方法。请记住，我的应用程序中同时包含收件箱和发件箱，发件人或收件人删除的消息对其他相关用户仍然可见，除非他们自行删除。

选项 1 - 简单的 ManyToMany:
表:
用户 - 只是用户字段
消息 - 只是消息字段
User_Message - 包含 2 个外键:user_id 和 message_id

示例:当用户发送一条消息时，在 Message 表中添加了一个消息行，并在 User_Message 中添加了两行，显然将发送者和接收者与添加的消息联系起来。
现在，这可能会有点当假设我只想获取收件箱消息时会出现问题，因为 ManyToMany 会获取所有这些消息，所以我想到了选项 2。

选项 2 - OneToMany:
表:
用户 - 只是用户字段
MessageReceived - 消息字段和 user_id 的外键
MessageSent - 消息字段和 user_id 的外键

示例:当用户发送消息时，该消息被添加到接收表和发送表中，但具有不同的user_id。当然，发件人 ID 将在发送表中，收件人 ID 将在接收表中。
现在，当我只想获取收件箱消息时，我正在从 MessageReceived 表中获取消息，并在删除例如收件箱(MessageReceived)消息时，它的副本仍然保留在 MessageSent 中并且可供发件人使用，所以一切都很好，但我觉得好像这个表有些“不酷”，因为我基本上在两个表中保留了几乎相同的数据。

请让我知道您对此有何看法，如果有任何更好的方法，我也在倾听。谢谢您的宝贵时间。

编辑:
Madbreaks 和 Tab Alleman 都提供了非常好的且有些相似的解决方案，非常感谢。我打算使用 Madbreaks 一个，只是因为我更喜欢删除连接表中的关系而不是保留“已删除”列，但这只是我的口味。尽管如此，还是感谢你们的时间和回答。

Answer 1

您不需要在 user_messages 中为每条消息添加 2 行 - 该表中有 3 列:sender_id、recipient_id、 message_id。

编辑

您在下面的问题中描述的删除场景改变了一些事情。您现在可能拥有两个 1 对 n 关系，而不是 n 对 n 方法:

1. 发件人与他们发送的许多消息之间的关系
2. 收件人与他们收到的许多邮件之间的关系

我可能会让消息表有一个发件人 ID 外键。然后我会有一个 message_recipients 表，将用户(收件人)ID 映射到消息 ID。

现在，如果发件人可以删除邮件，但收件人仍应能够访问它(并且知道发件人是谁)，那么您将需要四个表:

1. 用户
2. 消息
3. message_sender(一对一映射)——删除已发送消息的发件人从她那里删除
4. message_recipients (1-to-n map) -- 收件人删除收到的消息从这里删除

从您的问题中不清楚这是否是一项要求，我只是为了完整性而添加它。您可能需要触发器或后续查询来确定用户和消息表之间是否/何时没有剩余关系，并在那时(可能)删除消息本身。