对Web邮件系统数据库结构 [英] Database Structure for Web Messaging System

问题描述

我想打一个网页的邮件系统，如Facebook有。我已经想了数据库结构许多替代的，但不知道这是它的最佳实践。我有两个替代这里，第一个是使用两个表，第二个是用三个表，但使在ERD一个循环

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.

第一：两个表，其中消息表是指本身

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

二：三表，但要一个周期ERD

Second: Three Table, but make a cycle in 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

就个人而言，我很喜欢这种结构，因为它只能使用一个消息标题和许多信息（内容）。该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.

基本上，这两条表是一样的，但是这是实现这个消息系统的最佳做法是什么？之前谢谢你。

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

推荐答案

，我可以告诉你，分离和组织的事情只要有可能（我的最后一个项目......在次之前，）学习困难的方式后。自我关系是一件很好的事不近，如果可能的话（也有少数例外）。设计你的类在初审;建那么哪些东西合身的数据库，但保持简单的事情，因为他们应该的。我的preference是...好出鞘的比说，

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,

您可以preFER看到code。这是这里。结果
一个可能的查询从某个标题列表的邮件会

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);

• 您会看到我个人的preference比特字段。举例来说，你真的没有记住一定from_id超过一次，一旦你的目的是两个人的消息系统。


• 我希望你有疑问。



问候，

莱昂纳多

这篇关于对Web邮件系统数据库结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！