Web消息系统的数据库结构

I want to make an web messaging system like facebook have. I already think of many alternative for the database structure, but not sure which is the best practice for it. I have two alternative here, the first is using two table, the second is using three table but make a cycle in ERD.

我想制作像facebook这样的网络短信系统。我已经考虑过数据库结构的许多替代方案，但不确定哪种方法最适合它。我在这里有两个替代方案，第一个是使用两个表，第二个是使用三个表但是在ERD中进行循环。

First: Two Table, where the message table refer to itself

第一个：两个表，消息表引用自身

user
----------
id
name

message
--------------
id
from_id
to_id
message_id --> refer to this table itself, to make me know which message is the topic
subject
content
time
status --> inbox, outbox, archive
read --> read, unread

Second: Three Table, but make a cycle in erd

第二：三桌，但在erd中制作一个循环

user
----------
id
name

message_header
--------------
id
from_id
to_id
subject
status --> inbox, outbox, archive
time

message
--------
id
message_header_id
content
time
read --> read, unread
author_id

Personally, I like this structure, because it's only use one message header and many message (content). The author_id itself cannot be removed because I need it to know whether the message is at the left side (as a sender) or right side (as a receiver). This system is just for two person messaging system.

就个人而言，我喜欢这种结构，因为它只使用一个消息头和许多消息（内容）。 author_id本身无法删除，因为我需要它知道消息是在左侧（作为发送者）还是在右侧（作为接收者）。该系统仅适用于双人消息系统。

Basically this two table is the same, but which is the best practice to implement this messaging system? Thank you before.

基本上这两个表是相同的，但这是实现此消息传递系统的最佳实践？谢谢你。

1 个解决方案

#1

After learning the hard way (times ago, during my final project...), I can advise you to separate and organize the things whenever possible. Self-relationship is a nice thing not to be near, when possible (there are rare exceptions). Design your classes at first instance; build then a database in which things fit well, but keeping things simple as they should be. My preference is... better drawn than said,

在经历了艰难的学习之后（很久以前，在我最后的项目中...），我建议你尽可能地分开和组织这些事情。在可能的情况下，自我关系是一个不可靠的好事（很少有例外）。一开始就设计你的课程;然后构建一个数据库，在这个数据库中，事情很合适，但要保持简单。我的偏好是......比说得好，

Web消息系统的数据库结构

You may prefer to see the code. It's here.
A possible query to list messages from a certain header would be

您可能更愿意看到代码。它在这里。列出来自某个标题的消息的可能查询将是

SELECT
  h.id AS `header_id`, h.`subject`, h.`status`,
  m.id AS `message_id`, m.content, m.`time`,
  IF(m.is_from_sender, x.`name`, y.`name`) AS `written_by`
FROM (SELECT * FROM header WHERE id = @VAR) h
  INNER JOIN message m ON (h.id = m.header_id)
  INNER JOIN user x    ON (h.from_id = x.id)
  INNER JOIN user y    ON (h.to_id = y.id);

You'll see a personal preference of mine to bit fields. For instance, you don't really have to remember a certain from_id more than one time, once your purpose is a two person messaging system.
你会看到我个人偏爱比特领域。例如，一旦你的目的是双人消息传递系统，你就不必多次记住某个from_id。
I hope you have doubts.
我希望你有疑虑。

Regards,

问候，

Leonardo

莱昂纳多

#1

Web消息系统的数据库结构

You may prefer to see the code. It's here.
A possible query to list messages from a certain header would be

您可能更愿意看到代码。它在这里。列出来自某个标题的消息的可能查询将是

SELECT
  h.id AS `header_id`, h.`subject`, h.`status`,
  m.id AS `message_id`, m.content, m.`time`,
  IF(m.is_from_sender, x.`name`, y.`name`) AS `written_by`
FROM (SELECT * FROM header WHERE id = @VAR) h
  INNER JOIN message m ON (h.id = m.header_id)
  INNER JOIN user x    ON (h.from_id = x.id)
  INNER JOIN user y    ON (h.to_id = y.id);

You'll see a personal preference of mine to bit fields. For instance, you don't really have to remember a certain from_id more than one time, once your purpose is a two person messaging system.
你会看到我个人偏爱比特领域。例如，一旦你的目的是双人消息传递系统，你就不必多次记住某个from_id。
I hope you have doubts.
我希望你有疑虑。

Regards,

问候，

Leonardo

莱昂纳多

秒客网

Web消息系统的数据库结构

1 个解决方案

#1

#1

相关文章