CODEWITHSUNDEEP
sql-server-2008database-design
Code Ratchet
Code Ratchet

Reputation: 6029

Table setup for Messaging Functionality (email)

We've created an application whereby members of the public can search for each other and get in contact (think of it as a dating site) if they so desire, I'm currently in the process of building the Messaging functionality, but I'm curious on how I go about creating the table(s) in the database.

The current flow of the application is as follows:

User1 clicks on User2 to view his/her profile, scrolls down to the bottom of his/hers profile and types some text into a textarea and clicks send at this point I then pass in the data to the database I then send User2 an email saying "you have mail etc".

Taking that into consideration I would of assumed my Email table within SQL Server would look something like this:

Id (PK) (Increments by 1)

ToUserId (FK) // User who they're getting in contact with

FromUserId (FK) // User who sent the message

Content (nvarchar(3000)

Status (int) // read , new , deleted , sent

EmailDate (datetime)

EmailDeleted (datetime)

But the problem with this setup is both user's maybe sending / replying to each other so I would have multiple entries / statuses in one table which may become a nightmare to manage / control (unless I'm over thinking it)

I've spent a good few hours trying to come up with a solution from browsing on the web trying to gain knowledge for building messaging functionality yet it comes back very shy of results. Has anyone been able to build such functionality that wouldn't mind sharing knowledge with me.

Upvotes: 0

Views: 66

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82524

You can break it to two tables, something like this:

TblMessage
(
    Message_Id int identity(1,1) primary key,
    Message_SentDate datetime not null default(getDate()),
    Message_Title varchar(100),
    Message_Content varchar(max),
    Message_SenderId int, -- (fk to users)
    Message_IsDraft bit not null default(0), -- when 1 it's saved as draft.
    Message_IsDeletedFromOutbox bit not null default(0)-- when 1 don't show on sender outbox
)

TblMessageToRecipient
(
   MTR_UserId int, -- (fk to users)
   MTR_Message_Id int, -- (fk to message)
   MTR_ReadDate datetime null, (if null then status is new)
   MTR_DeleteDate datetime null, (if not null then status is deleted)
   PRIMARY KEY (MTR_UserId, MTR_Message_Id)
)

This way you can give the recipient an option to "delete forever" a message and just delete the relevant record from TblMessageToRecipient.
Also, you can delete the message completely from tblMessage if it doesn't have a reference on the TblMessageToRecipient and Message_DeletedFromOutbox = 1 (this can be done by a scheduled sql agent job to prevent tblMessages from getting too big)

update:

I hope this will answer your question in the comment:

The recipient has several possible statuses:

  1. New - when MTR_ReadDate is null.
  2. Read -when MTR_ReadDate is not null, and MTR_DeleteDate is null.
  3. Recycled - when MTR_DeleteDate is null.
  4. Deleted - when the record is deleted from TblMessageToRecipient.

The sender have only 3 possible statuses:

  1. Draft.
    I've added a bit column to the TblMessage called IsDraft. note that drafts should also save recipient information, so it should be saved in both tables, just show it to the sender in the drafts box, and don't show it to the recipient. Note that when the sender discard the draft you should delete the message from both tables.

  2. Sent.
    Once the message is in both tables, and IsDraft = 0, and IsDeletedFromOutbox = 0, it means that the message was sent. in this case, show it to the sender in the sent messages box, and show it to the recipient.

  3. Deleted from outbox.
    When IsDeletedFromOutbox = 1 you simply don't show the message to the sender.
    if the message record does not have any references in the TblMessageToRecipient, you can delete the record from TblMessages since it was deleted by the sender and by all of it's recipients.

Update 2:
To summerize our conversation in the comments, there are 2 mahor ways to keep a conversation structure (meaning the link between a message and a reply to it [and it's reply and so on...])

One way is to keep a Message_ParentId nullable column in TblMessages. This column will contain null for any message that is not a reply to an older message, but for replies it will contain the message id of the message it was a reply to.

The second way is to keep a Message_ConversationId column that will always contain a value. when a message is a reply to an older message, it's Message_ConversationId should be the same as it's parent message. When it's not a reply, it's ConversationId should be generated. Since we are talking about sql server 2008, it means the easiest way to generate a new conversation id every time will be to add a new table called TblConversation. this table can keep a single column Conversation_Id that will be an int identity column, and to get a new conversation id do something like this:

DECLARE @ConversationId int
INSERT INTO TblConversation DEFAULT VALUES
SELECT @ConversationId = SCOPE_IDENTITY()

and then user the @ConversationId when inserting a new root message.

Upvotes: 1

Related Questions