SQL 电子邮件消息系统的数据库设计

Database design for email messaging system

提问by AsifQadri

I want to make an email messaging system like gmail have. I would like to have following option: Starred, Trash, Spam, Draft, Read, Unread. Right now I have the below following structure in my database :

我想制作一个像 gmail 一样的电子邮件消息系统。我想有以下选项：加星标、垃圾邮件、垃圾邮件、草稿、已读、未读。现在我的数据库中有以下结构：

CREATE TABLE [MyInbox](
    [InboxID] [int] IDENTITY(1,1) NOT NULL,
    [FromUserID] [int] NOT NULL,
    [ToUserID] [int] NOT NULL,
    [Created] [datetime] NOT NULL,
    [Subject] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Body] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [IsRead] [bit] NOT NULL,
    [IsReceived] [bit] NOT NULL,
    [IsSent] [bit] NOT NULL,
    [IsStar] [bit] NOT NULL CONSTRAINT [DF_MyInbox_IsStarred]  DEFAULT ((0)),
    [IsTrash] [bit] NOT NULL CONSTRAINT [DF_MyInbox_IsTrashed]  DEFAULT ((0)),
    [IsDraft] [bit] NOT NULL CONSTRAINT [DF_MyInbox_Isdrafted]  DEFAULT ((0))
) ON [PRIMARY]

But I am facing some issues with the above structure. Right now if a user A sends a msessage to user B I am storing a row in this table But if user B deletes the that message it gets deleted frm user's A sent message too. This is wrong, I want exactly as normal email messaging system does. If A deletes message from his sent item then B should not get deleted from his inbox. I am thinking on other problem here which will come suppose a user A sent a mail to 500 users at once so as per my design I will have 500 rows with duplicate bodies i.e not a memory efficent way to store it. Could you guys please help me in makeing the design for a messaging system ?

但是我在上述结构中遇到了一些问题。现在，如果用户 A 向用户 BI 发送消息，则正在此表中存储一行，但如果用户 B 删除该消息，它也会从用户 A 发送的消息中删除。这是错误的，我想要和普通的电子邮件消息系统一样。如果 A 从他发送的项目中删除消息，那么 B 不应从他的收件箱中删除。我正在考虑这里的其他问题，假设用户 A 一次向 500 个用户发送了一封邮件，因此根据我的设计，我将有 500 行具有重复的正文，即不是一种内存有效的存储方式。你们能帮我设计一个消息系统吗？

回答by Raghav

You need to split your table for it. You could have following schema and structure

你需要为它拆分你的桌子。您可以具有以下架构和结构

CREATE TABLE [Users]
    (
      [UserID] INT ,
      [UserName] NVARCHAR(50) ,
      [FirstName] NVARCHAR(50) ,
      [LastName] NVARCHAR(50)
    )

CREATE TABLE [Messages]
    (
      [MessageID] INT ,
      [Subject] NVARCHAR(MAX) ,
      [Body] NVARCHAR(MAX) ,
      [Date] DATETIME,
      [AuthorID] INT,
    )

CREATE TABLE [MessagePlaceHolders]
    (
      [PlaceHolderID] INT ,
      [PlaceHolder] NVARCHAR(255)--For example: InBox, SentItems, Draft, Trash, Spam 
    )

CREATE TABLE [Users_Messages_Mapped]
    (
      [MessageID] INT ,
      [UserID] INT ,
      [PlaceHolderID] INT,
      [IsRead] BIT ,
      [IsStarred] BIT 

    )

In users table you can have users."Messages" denotes the table for messages. "MessagePlaceHolders" denotes the table for placeholders for messages. Placeholders can be inbox, sent item, draft, spam or trash. "Users_Messages_Mapped" denotes the mapping table for users and messages. The "UserID" and "PlaceHolderID" are the foreign keys."IsRead" and "IsStarred" signifies what their name stands for. If there is no record found for a particular messageid in "Users_Messages_Mapped" table that record will be deleted from Messages table since we no longer need it.

在用户表中，您可以拥有用户。“消息”表示消息表。“MessagePlaceHolders”表示消息占位符表。占位符可以是收件箱、已发送邮件、草稿、垃圾邮件或垃圾箱。“Users_Messages_Mapped”表示用户和消息的映射表。“UserID”和“PlaceHolderID”是外键。“IsRead”和“IsStarred”表示它们的名称代表什么。如果在“Users_Messages_Mapped”表中没有找到特定 messageid 的记录，该记录将从 Messages 表中删除，因为我们不再需要它。

回答by PaulJWilliams

I think you need to decompose your schema some more. Store emails seperately, and map inboxes to the messages they contain.

我认为您需要进一步分解您的架构。单独存储电子邮件，并将收件箱映射到其中包含的消息。

回答by Tim McNamara

If you're doing document-orientated work, I suggest taking a look at CouchDB. It is schema-less, meaning issues like this disappear.

如果您正在做面向文档的工作，我建议您查看CouchDB。它是无模式的，这意味着这样的问题会消失。

Let's take a look at the example: A sends a message to B, and it's deleted by B.

我们来看一个例子：A向B发送消息，被B删除。

You would have a single instance of the document, with recipientslisted as an attribute of the email. As users delete messages, you either remove them from the recipients list or add them to a list of deleted_byor whatever you choose.

您将拥有该文档的单个实例，并recipients作为电子邮件的一个属性列出。当用户删除邮件时，您可以将它们从收件人列表中删除，或者将它们添加到deleted_by您选择的列表中。

It's a much different approach to data than what you're used to, but may be highly beneficial to take some time to consider.

这是一种与您习惯的数据方法大不相同的方法，但花一些时间考虑可能非常有益。

回答by Maximus

If I were you I would set two flags one for sender and other one for receiver if both flags are true then message should be deleted from database otherwise keep that in database but hide it from who deleted it.

如果我是你，我会设置两个标志，一个用于发送者，另一个用于接收者，如果两个标志都为真，那么应该从数据库中删除消息，否则将其保留在数据库中，但对删除它的人隐藏。

Do same thing for trash. You may want to run cron or check manually if both sender and receiver delete the message then remove it from database.

对垃圾做同样的事情。您可能需要运行 cron 或手动检查发送方和接收方是否都删除了消息，然后将其从数据库中删除。

回答by nvogel

You could create a table for MessageContacts which joins each message to the people who have it in their mailboxes. When a user deletes a message then a row gets deleted from MessageContacts but the original message is preserved.

您可以为 MessageContacts 创建一个表，该表将每条消息与邮箱中拥有该消息的人联系起来。当用户删除消息时，从 MessageContacts 中删除一行，但保留原始消息。

You could do that... but I suggest you don't. Unless it's an academic exercise set by your tutor then it is surely a complete waste of time to develop your own messaging system. If it is homework then you ought to say so. If not, then go do something more useful instead.

你可以这样做...但我建议你不要。除非它是由您的导师设置的学术练习，否则开发自己的消息传递系统肯定是完全浪费时间。如果是家庭作业，那么你应该这么说。如果没有，那就去做一些更有用的事情吧。

回答by Gaurav Singhal

CREATE TABLE `mails` (  
  `message_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,  
  `message` varchar(10000) NOT NULL DEFAULT '',  
  `file` longblob,  
  `mailingdate` varchar(40) DEFAULT NULL,  
  `starred_status` int(10) unsigned NOT NULL DEFAULT '0',  
  `sender_email` varchar(200) NOT NULL DEFAULT '',  
  `reciever_email` varchar(200) NOT NULL DEFAULT '',  
  `inbox_status` int(10) unsigned NOT NULL DEFAULT '0',   
  `sent_status` int(10) unsigned NOT NULL DEFAULT '0',  
  `draft_status` int(10) unsigned NOT NULL DEFAULT '0',  
  `trash_status` int(10) unsigned NOT NULL DEFAULT '0',  
  `subject` varchar(200) DEFAULT NULL,  
  `read_status` int(10) unsigned NOT NULL DEFAULT '0',  
  `delete_status` int(10) unsigned NOT NULL DEFAULT '0',  
  PRIMARY KEY (`message_id`)  
)

You can use this table for storing the mails and manipulate the queries according to mail boxes. I am avoiding rest of the tables like user details and login details table. You can make them according to your need.

您可以使用此表来存储邮件并根据邮箱操作查询。我避免使用其他表格，如用户详细信息和登录详细信息表。您可以根据需要制作它们。

回答by P. Lusine

in my structure, I set "deleted: bool" flag and depend on its value show message or hide.

在我的结构中，我设置了“deleted: bool”标志并根据其值显示消息或隐藏。

回答by Elvin Nagiyev

WHY DELETE? I think there is no need to delete anything. Just hide it, from users when deleted. Because, it will problem to check both sides, when sender send same message to many recipients. Then you have to check and flag all recipients. If all OK, then delete... I think there is no need to delete anything.

为什么要删除？我认为没有必要删除任何东西。只需将其隐藏，删除时对用户不可见。因为，当发件人向许多收件人发送相同的消息时，检查双方会出现问题。然后您必须检查并标记所有收件人。如果一切正常，则删除...我认为没有必要删除任何内容。

回答by tdammers

A message can only be in one folder at a time, so you want a folders table (containing folders 'Trash', 'Inbox', 'Archive', etc.) and a foreign key from messages to folders. For labels, you have a many-to-many relation, so you need a labels table and also a link table (messages_labels). For starring, a simple bit column should do, same for 'unread'.

一封邮件一次只能在一个文件夹中，因此您需要一个文件夹表（包含文件夹“垃圾箱”、“收件箱”、“存档”等）和一个从邮件到文件夹的外键。对于标签，您有一个多对多的关系，因此您需要一个标签表和一个链接表 (messages_labels)。对于主演，应该做一个简单的位列，“未读”也是如此。