我希望建立一个PM(个人消息传递)系统,但一直在努力建立一个流体数据库,将我的'user'表的用户与消息表联系起来。
在我的PM系统中,如果您要进入界面,您将看到发件人的头像,发件人的姓名和发件人的消息。然而,数据库将拥有发送者和接收者的名称、消息的内容以及发送消息的时间戳。数据库还将跟踪邮件是否已从用户的收件箱中删除。
下面是我所做的:设置了三个表('users'表,'messages'表)。'users'表包含所有主id为auto_increment的注册用户。"messages"表包含一个auto_increment的主message_id,一个包含user_id的行,一个包含发送消息的TIMESTAMP的行,以及一个包含message_content的行。我的设置在满足我想要的是正确的吗?我遇到的问题是发件人的消息没有与预期的收件人链接(事实上,我甚至不知道消息的去向)。
当你试图编写一个数据库模式时,你必须像考虑实体(事物)那样考虑表。表的作用是描述一件事物或事物的一部分。该描述由属性(列)组成。每行只能描述一件事或一件事的一部分(这意味着多个表可以表示一件事的各个部分)。这叫做数据库规范化。
所以在你的情况下,你有三个主要的事情要关心。
- 用户
- 收件箱
如果你考虑这三件事之间的关系,你可以得出结论,你的模式基本上只是一组你知道你以后会问的问题的答案的框架。
例如,如果每个用户都必须有一个收件箱,并且每个收件箱都可以有消息,那么Inbox
模式需要有一个user_id
列,该列允许您识别哪个收件箱属于哪个用户。此外,由于收件箱可能有一条或多条消息,因此它还必须包含inbox_id
(这将是您的自动增量id),这将允许您在表中识别唯一的收件箱。显然,Message
模式还需要一个message_id
列来惟一地标识每个消息。模式还需要一个user_id
列来标识消息属于哪个用户(即消息的作者)。
然而,由于Inbox
和Message
之间存在一对多关系,User
和Message
之间存在多对多关系,因此您无法在同一表中轻松描述它们之间的关系,而不会造成逻辑不一致。这被称为3NF或第三范式。
因此,您将创建第四个表来简单地描述收件箱与其消息之间的关系。我们暂且称其为Recipient
表。
Recipient
表需要知道收件箱和消息,以及用户表中的哪个用户是该消息的接收者。这意味着你需要3个密钥或主键。
-
inbox_id
-
message_id
-
user_id
//这将是接收者的id
请记住,该列表中的3是发送消息的用户的id,而不是写消息的用户的id(该用户已经由Message
表标识)。
现在,当您想知道哪些消息在给定用户的收件箱中,您只需查询Recipient
并将其加入User
和Message
,就像这样…
SELECT mesage.user_id AS Sender, message.contents, recipient.user_id AS Recipient
FROM recipient, inbox
JOIN message ON recipient.message_id = message.id
WHERE inbox.user_id = ?