database-design - 用于电子邮件或私有(private)消息的良好数据库设计

Question

现在，我正在为我的网站使用消息系统。该消息用于在站点成员之间发送消息。它也可以用来发送好友邀请等。但是，我还没有找到一个好的数据库设计。我希望消息系统使用 线程样式 ，就像电子邮件一样。但由于我不是复杂数据库设计的专家，我不知道该怎么做。

到目前为止，这是我的设计，

CREATE TABLE messages (
  message_id            BIGINT      PRIMARY KEY,
  message_date_time     TIMESTAMP   DEFAULT NOW(),
  message_subject       TEXT,
  message_body          TEXT,
  message_attachment    TEXT, -- path to attachment folder
  message_sender_id     INT, -- FK to table user
  message_sender_status INT, -- 0 = deleted by sender, 1=default (can be seen on sender outbox)
);

还有一张 table ……

CREATE TABLE message_recipients (
  message_id               BIGINT, -- FK to table messages
  message_recipient_id     INT,    -- FK to table user
  message_recipient_status INT,    -- 0=deleted from recipient inbox, 1=new message, 2=read
);

我相信我需要另一个表来存储消息之间的链接，这就是我需要这些的原因

CREATE TABLE message_reply (
  message_id        BIGINT, -- FK to table messages
  message_to_reply  BIGINT, -- FK to table messages
);

但这些表只会让我难以在我的 PHP 页面上查询和处理。
我只希望用户可以观察源电子邮件和回复(如 GMAIL 或 Facebook 的墙)...
有更好的建议吗？

Additional description

I want the message can be sent to many recipient. But once it sent, it cannot be modified. Let's say I sent a message to X, Y, and Z. When X reply, there will be a message from X in my inbox. And if Z reply, there will be a message from Z in my inbox, with same subject with X (e.g. RE: subject of my first mail). When I choose X's message, there will be X's message, followed by my first message. If I choose to sent X a reply, X will receive a message contains my reply, her reply, and my first message. Whether I reply Z or Z reply me again, that will be another matter from X. X cannot see what Z wrote and on the other hand, Z cannot see the conversation between me and X. Complicated isn't it? That's why i on the verge of death because of these matter. Sigh -_-,

   

谢谢
托尼

Answer 1

如果消息由 1 人发送且仅由 1 人接收，则您不需要链接表，您只需这样做:

message
- message_id
- recipient_id -> links to user.user_id
- sender_id -> links to user.user_id

user
- userid

如果消息可以是对另一条消息的回复，只需在消息本身中添加一个字段来说明它是对以下消息的回复:

message
- parent_id -> message.message_id, or null if it isn't a reply.

这是一个非常简单的设置，如果您要对其进行大量扩展，这并不是最佳选择，但这很简单。