PHP / MySQL PM系统具有多个收件人表结构？
我目前正在为一个网站开发一个私信系统，其中用户必须能够向多个收件人发送私信。当然，这意味着如果消息已发送给用户 A、B 和 C，则用户 C 可以删除该消息，而用户 A 和 B 则不会。 问题是对于这样一个系统来说，最好的数据库表结构是什么，当然要避免一条消息的多个副本。 目前我想到了这个表结构：
msgid (int),
parentid (int),
timestamp (timestamp),
senderid (int),
recipients (varchar),
subject (varchar)
text (text),
deletedby (varchar),
readby (varchar)
这将是唯一的表。线程是根据parentid 创建的（如果没有parentid，则消息是线程中的第一个消息），并按时间戳排序。收件人以逗号分隔存储在一列中，并使用 WHERE userid IN (msg.recipients) 进行检查。 Deleteby 列包含已删除消息的用户的所有 ID（以逗号分隔），就像 readby 列一样。
但是我不确定这是否是理想的表结构。在开始编码之前，我想听听您的改进想法。
I'm currently developing a PM system for a website, in which users must be able to send PM's to multiple recipients. Naturally, this means that if a message has been sent to user A, B and C, user C could delete the message while user A and B won't.
The question is what would be the best database table structure for such a system, of course avoiding multiple copies of one message.
Currently I've thought of this table structure:
msgid (int),
parentid (int),
timestamp (timestamp),
senderid (int),
recipients (varchar),
subject (varchar)
text (text),
deletedby (varchar),
readby (varchar)
This would be the only table. Threads are created based on the parentid's (if there is no parentid the message is the first in a thread), and ordered by timestamp. Recipients are stored comma-separated in one column and checked by using WHERE userid IN (msg.recipients). The deletedby column contains all id's (comma-separated) of users which have deleted the message, just like the readby column.
However I am not sure if this is an ideal table structure. Before I start coding I would like to hear your thoughts for improvements.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
将 CSV 值存储在单个字段中总是一个糟糕的设计，只会给您带来严重的痛苦。在将系统推出生产之前，现在规范设计：将收件人列表放入子表中，并在子记录上放置“已删除”平面以指示特定收件人是否删除了邮件：
...或类似的东西。
Storing CSV values in a single field is invariably a bad design and will just cause you severe pain. Normalize the design now, before you roll out the system for production: put the recipients list into a child table, and put a "deleted" flat on the child record to indicate whether that particular recipient deleted the message or not:
... or something similar.