电子邮件系统的数据库设计 [英] Database design for email messaging system

问题描述

我想制作像Gmail这样的电子邮件系统。我想要有以下选项：加星标，垃圾邮件，垃圾邮件，草稿，阅读，未读。现在我在数据库中有以下结构：

  CREATE TABLE [MyInbox]（
 [InboxID] [ int] IDENTITY（1,1）NOT NULL，
 [FromUserID] [int] NOT NULL，
 [ToUserID] [int] NOT NULL，
 [Created] [datetime] NOT NULL， 
 [主题] [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] 
  

但是我面临上述结构的一些问题。现在如果用户A向用户B发送一个消息，我正在该表中存储一行。但是如果用户B删除该消息，那么它也被删除了frm用户的A发送消息。这是错误的，我想像正常的电子邮件系统那样。如果A从他发送的项目中删除消息，则B不应该从他的收件箱中删除。我正在考虑这里的其他问题，这将假定用户A一次向500个用户发送邮件，因此根据我的设计，我将拥有500行的重复文本，而不是存储效率较高的存储方式。你可以帮我做一个消息系统的设计吗？

解决方案

你需要拆分你的表。您可以拥有以下架构和结构

  CREATE TABLE [Users] 
（
 [UserID] INT， 
 [UserName] NVARCHAR（50），
 [FirstName] NVARCHAR（50），
 [LastName] NVARCHAR（50）
）
 
 CREATE TABLE [消息] 
（
 [MessageID] INT，
 [主题] NVARCHAR（MAX），
 [Body] NVARCHAR（MAX），
 [Date] DATETIME， 
 [AuthorID] INT，
）
 
 CREATE TABLE [MessagePlaceHolders] 
（
 [PlaceHolderID] INT，
 [PlaceHolder] NVARCHAR 255） - 例如：Inbox，SentItems，Draft，Trash，Spam 
）
 
 CREATE TABLE [Users_Messages_Mapped] 
（
 [MessageID] INT，
 [UserID] INT，
 [PlaceHolderID] INT，
 [IsRead] BIT，
 [IsStarred] BIT 
 
）
  

数据库图：
alt text http://codeasp.net/Assets/Uploaded-CMS-Files/13f15882-7ed9-4e22-8e2c- 20c6527522317-31-2010％2012-51-50％20 AM.png

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

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 :

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 ?

解决方案

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

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.

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