0

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.

Improve this question
2
  • why would that be a nightmare? seems like a legit design to me. Commented Jul 21, 2015 at 11:49
  • @ZoharPeled This is the first time I'm creating this functionality so I'm a bit optimistic in regards to jumping in a creating the table, another issue I see with what I have supplied is If User2 receives the email and then deletes the email, it will be deleted from User2 sent box because its linked.... I just need some fresh eyes on this as I've been looking at it for a while... Commented Jul 21, 2015 at 12:00

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

apologies on not coming back sooner, I've was dragged on to another project, I'm looking at what you've provided but how / where does it separate the different statuses for both users i.e User1 sends the email so that would have a status of 1 for example and it would appear in his Sent box, User2 reads it and then deletes it so it would have a status of 2 and would appear in her delete box ? this is what I'm getting confused about..
thanks for providing an update, sadly I'm still confused, I'm building a very basic messaging program, so I wont have the drafts, recycled or title I'm trying to get a better grasp on this can you tell me if I have the correct thought. I contact someone through the site, It inserts a record into the TblMessage table, outputs the insertedId (Message_Id) I then take this Message_Id and insert a record into TblMessageToRecipient UserId being the person I want to contact?
Just on the back on my comment few minutes ago, this table will potentially grow to a incredible size, what would you recommend for helping with performance i.e looks ups etc I know we have the PK and FK is there anything else I could take into consideration?
"premature optimization is the root of all evil"... don't worry too much about performance now, write readable code and don't be cheep with comments, so that you can understand it when you read it 2 years from now. Keep in mind that you can always add indexing to the tables.
Ok thanks, in regards to what I said I.e outputting the inserted I'd etc is that the correct flow?
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.