SQL Server Database design for email messaging system

kh212irz  于 2023-08-02  发布在  其他
关注(0)|答案(9)|浏览(148)

I want to make an email messaging system like Gmail. I want to have the options: Starred, Trash, Spam, Draft, Read, Unread. 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]

When user A sends a message to user B I store it in this table. But if user B deletes that message it gets deleted from user A's sent messages too. This is wrong, if A deletes the message from his sent items then B should not get it deleted from his inbox either.

Another problem is when user A sent a mail to 500 users I have 500 duplicates (not an efficient way to store). How do I correctly design an email messaging system?

2exbekwf

2exbekwf1#

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.

gc0ot86w

gc0ot86w2#

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

Let's take a look at the example: A sends a message to B, and it's deleted by 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.

ztmd8pv5

ztmd8pv53#

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

w6lpcovy

w6lpcovy4#

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.

vkc1a9a2

vkc1a9a25#

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'.

pdtvr36n

pdtvr36n6#

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.

tct7dpnv

tct7dpnv7#

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.

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.

u3r8eeie

u3r8eeie8#

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.

xienkqul

xienkqul9#

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

相关问题