July 21, 2015 at 3:41 am
I'm in the process of building messaging functionality in to my application where by users can contact one another, look at it as a dating site, you click on someones profile, view their profile and then send that user a message.
I started to build the table which looked 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)) -- Message being send
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.
I have looked on the web for building messaging functionality yet it comes back very shy of results, has anyone done something similar if not the same and are willing to share table designs?
July 21, 2015 at 7:11 am
.Netter (7/21/2015)
I would have multiple entries / statuses in one table which may become a nightmare to manage / control.
Why is that a problem? Tables are designed to have multiple entries (called rows). Each message should only have one status at a time, so I can't see the problem.
July 21, 2015 at 7:39 am
Luis Cazares (7/21/2015)
.Netter (7/21/2015)
I would have multiple entries / statuses in one table which may become a nightmare to manage / control.Why is that a problem? Tables are designed to have multiple entries (called rows). Each message should only have one status at a time, so I can't see the problem.
Each entry would have a different email date and a different Id.
Like Luis said, That should not be a problem. That's how database tables are usually used.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 21, 2015 at 9:57 am
Do not cluster this table by identity. Cluster it instead on EmailDate, for example.
I'd strongly consider using nvarchar(max) rather than nvarchar(3000) to allow longer messages if needed.
Status should not need to be a full integer -- tinyint should be enough.
Rather than an "emaildeleted" column, I'd simply move it to a deleted table.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 21, 2015 at 10:38 am
You may want to consider adding a Subject field and an EmailChain ID field. Most applications that provide this type of functionality include a subject line. An EmailChain ID would enable you to write code that supported functionality similar to MS Outlook's "Find all related messages".
-- Itzik Ben-Gan 2001
July 21, 2015 at 12:12 pm
Similar to what Alan said, consider adding a ParentID column that's a foreign key to the table's primary key. When posting an original message, the ParentID would be NULL. When replying to a message, the ParentID would contain the ID of the message being responded to.
A message chain can be looked at as a tree structure. The ParentID would allow you to traverse the tree in either direction.
July 21, 2015 at 4:23 pm
Hi All,
Thanks for the responses, I would like to touch base on that you mentioned regarding a parent table.
How would this parent table look? i.e what columns would it have?
When I was looking at my original table design I did notice something say User1 sent an email to User2, User2 then viewed the email and deleted it, it would then delete it from User1 mailbox as well, when in fact the message should appear in User1 Sent box. And as User2 Deleted it then it should appear in User2 deleted box.
This is what I'm struggling to grasp.
July 22, 2015 at 5:11 am
I meant that a ParentID column would reference the same table to point to the parent message of the current row. It would be NULL for the original message in a thread.
July 22, 2015 at 6:21 am
Hi thanks for coming back to me, would you be able to provide me with a code sample as I'm trying to grasp your concept , also would your solution cater for what I mention in my previous comment regarding deleting / sent etc?
July 22, 2015 at 6:36 am
.Netter (7/22/2015)
Hi thanks for coming back to me, would you be able to provide me with a code sample as I'm trying to grasp your concept , also would your solution cater for what I mention in my previous comment regarding deleting / sent etc?
I didn't address the point of deleting or how to display the messages in the interface. Here's an attempt to illustrate the point about the ParentID, using the columns in your OP. I assumed that your table of users is named dbo.Users.
CREATE TABLE dbo.UserMessages (
ID integer not null identity(1, 1),
constraint UserMessages_PK
primary key (ID),
ParentID Integer,
constriant UserMessages_UserMessages_FK
foreign key (ParentID)
references dbo.UserMessages(ParentID),
FromUserID Integer not null,
constraint UserMessages_Users_From_FK
foreign key (FromUserID)
references dbo.Users(ID),
ToUserID Integer not null,
constraint UserMessages_Users_To_FK
foreign key (ToUserID)
references dbo.Users(ID),
Content nvarchar(3000)
Status Integer not null,
EmailDate Datetime not null,
EmailDeleted Datetime);
The ParentID column is a foreign key to the ID column. This allows you to know what message was being replied to or forwarded, but you'd also have to add a column to know if it was a reply or forward. The original message in a thread would have the ParentID column NULL. I hope this makes it clearer.
Take a look at your Outlook. Try to determine what the table structure is behind it. Granted, they have things like read receipts, attachments, flags, etc., but try to focus on just the functionality you want to provide in your messaging system. Depending on what you want to provide, this might be bigger than you initially thought. Then again, I find that many things are bigger than they first appear. 😉
July 22, 2015 at 3:59 pm
Hi thanks for the sql script, I was looking at outlook actually trying to figure out how it works etc, I won't be interested in read receipts, flags, or anything like that its just a standard messaging function whereby the user can send, delete, and reply to emails.
I shall play around with what you have provided and come back shortly if I have any concerns
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply