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

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向用户B发送msessage，我将在这个表中存储一行，但是如果用户B删除该消息，它也将被删除frm用户的a发送消息。这是错误的，我想要和普通的电子邮件信息系统一样。如果从他发送的邮件中删除一条消息，那么B就不应该从他的收件箱中删除。我正在考虑另一个问题，假设一个用户a同时给500个用户发了邮件，根据我的设计，我将有500行和重复的body I。这不是一种有效的储存记忆的方法。你们能帮我设计一个信息系统吗?

8 个解决方案

#1

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 

    )

Database Diagram: alt text http://codeasp.net/Assets/Uploaded-CMS-Files/13f15882-7ed9-4e22-8e2c-20c6527522317-31-2010%2012-51-50%20AM.png

数据库图:alt文本http://codeasp.net/assets/uploaded - cms - files/13f15882-7ed9 -4e22- 8e22 -8e2c-20c6527522317-31-2010%2012-51-50% 20am.png

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.

在users表中可以有users。“message”表示消息的表。“messageplaceholder”表示消息占位符的表格。占位符可以是收件箱、发送项、草稿、垃圾邮件或垃圾。“users_messages_mapping”表示用户和消息的映射表。“UserID”和“PlaceHolderID”是外键。“IsRead”和“isstar”表示他们的名字代表什么。如果在“users_messages_mapping”表中没有找到特定messageid的记录，则该记录将从消息表中删除，因为我们不再需要它。

#2

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 recipients listed 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_by or whatever you choose.

您将拥有文档的一个实例，其中收件人作为电子邮件的属性列出。当用户删除消息时，您要么将它们从收件人列表中删除，要么将它们添加到删除的列表中，或者您选择的任何内容。

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.

这是一种与以往不同的数据处理方法，但花一些时间考虑可能会非常有益。

#3

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

我认为您需要进一步分解您的模式。分开存储电子邮件，并将收件箱映射到它们包含的消息。

#4

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或手动检查发送方和接收方是否删除消息，然后从数据库中删除消息。

#5

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.

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

#6

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_label)。对于主演，一个简单的位列就可以了，“未读”也是一样。

#7

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.

为什么删除?我认为没有必要删除任何东西。只要隐藏它，当用户删除时。因为，当发件人向许多收件人发送相同的消息时，就会出现两边检查的问题。然后，您必须检查并标记所有收件人。如果一切正常，那么删除…我认为没有必要删除任何东西。

#8

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.

您可以使用此表存储邮件，并根据邮件框操作查询。我避免使用其他表，如用户详细信息和登录细节表。你可以根据你的需要来做。