Database design for email messaging system

Viewed 37501

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 ?

9 Answers

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.

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

Related