Internal Messaging System Structure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Internal Messaging System Structure

Author

Topic

scripter
Starting Member

49 Posts

Posted - 2010-03-19 : 11:30:19

for years now I have had a problem with trying to design the best database design for an internal email messaging system for a website.

I would have thought that this would be a fairly easy thing to think of at this point in my development history but it still eludes me to no end.

I need the database schema to make this the most like a normal email system. where if you delete a message I send you from your inbox i still see it in my sent box.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-19 : 12:57:25

don't actually delete it. add a status field and update that. something like:

1 = New
2 = Sent
3 = Read
4 = Deleted
etc.

I'd put the possible valid statuses in their own table and use a foreign key to ensure that only valid statuses are in the message table

Kristen
Test

22859 Posts

Posted - 2010-03-19 : 13:10:27

We store messages in one table (the MESSAGE table), and links between MessageID and PersonID in another table (the LINK table).

Thus one Message record can be viewed by several "recipients" and the original "author".

The LINK table also holds status code for Unread, Read, Deleted. Once a message is marked as Deleted by all Linked people then it, and the Links, can be physically deleted.

We also use this for announcements - a message that is broadcast to everyone. Its one message record, and a Link record for everyone in the Person table, but we flag it differently so that it shows up in the banner at the top of every page,a nd we delete it after some expiry date (regardless of whether everyone has marked it as Read/Delete, or not.

scripter
Starting Member

49 Posts

Posted - 2010-04-06 : 12:19:17

Ok well I have been doing alot of research on this and I have decided that I wanted to make things difficult and beable to display a list of replies kind of like how google does with their gmail. Well I know I need to create a CTE Query and I thought I did but I don't believe it is working actually i know it is not working.

HERE IS THE SP
CREATE PROCEDURE [dbo].[DisplayEmailGroup]
-- Add the parameters for the stored procedure here
@UserID bigint,
@ParentID bigint

AS
BEGIN
DECLARE @TempTab Table([MailID] bigint,[Subject] nvarchar(50),[Body] text,[ParentID] bigint,[UserTo] nvarchar(50),[UserFrom] nvarchar(50),[UserID] bigint,[Trash] bit,[PermDelete] bit,[ImportanceLevel] nvarchar(50),[FromUserID] bigint,[ToUserID] bigint);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

WITH EmailAnchor([MailID],[Subject],[Body],[ParentID],[UserTo],[UserFrom],[UserID],[Trash],[PermDelete],[ImportanceLevel],[FromUserID],[ToUserID], Level)
AS
(
SELECT mt.MailID, mt.[Subject], mt.Body, pc.ParentID
, UTTo.UserName AS UserTo
, UTFrom.UserName AS UserFrom
, UTFrom.UserID
, d.Trash, d.PermDelete,
i.ImportanceLevel, mf.FromUserID, mto.ToUserID, 0 AS Level
FROM dbo.MailTbl AS mt INNER JOIN
dbo.MailParentChildTbl AS pc ON mt.MailID = pc.MailID
INNER JOIN
dbo.MailFromTbl AS mf ON mt.MailID = mf.MailID
INNER JOIN
dbo.MailToTbl AS mto ON mt.MailID = mto.MailID INNER JOIN
dbo.UserTable AS UTFrom ON mf.FromUserID = UTFrom.UserID
INNER JOIN
dbo.UserTable AS UTTo ON mto.ToUserID = UTTo.UserID
INNER JOIN
dbo.MailDeleteTbl AS d ON mto.ToUserID=d.UserID
INNER JOIN
dbo.MailDeleteTbl AS de ON mf.FromUserID = de.UserID
INNER JOIN
dbo.MailImportance AS mi ON mt.MailID = mi.MailID
INNER JOIN
dbo.ImportanceLevels AS i ON mi.ImportanceLevelID = i.ImportanceLevelID
WHERE ParentID IS NULL

UNION ALL
SELECT mt.MailID, mt.[Subject], mt.Body, pc.ParentID
, UTTo.UserName AS UserTo
, UTFrom.UserName AS UserFrom
, UTFrom.UserID
, d.Trash, d.PermDelete,
i.ImportanceLevel, mf.FromUserID, mto.ToUserID, Level + 1
FROM dbo.MailTbl AS mt INNER JOIN
dbo.MailParentChildTbl AS pc ON mt.MailID = pc.MailID
INNER JOIN
dbo.MailFromTbl AS mf ON mt.MailID = mf.MailID
INNER JOIN
dbo.MailToTbl AS mto ON mt.MailID = mto.MailID INNER JOIN
dbo.UserTable AS UTFrom ON mf.FromUserID = UTFrom.UserID
INNER JOIN
dbo.UserTable AS UTTo ON mto.ToUserID = UTTo.UserID
INNER JOIN
dbo.MailDeleteTbl AS d ON mt.MailID = d.MailID
INNER JOIN
dbo.MailDeleteTbl AS de ON UTFrom.UserID = de.UserID
INNER JOIN
dbo.MailImportance AS mi ON mt.MailID = mi.MailID
INNER JOIN
dbo.ImportanceLevels AS i ON mi.ImportanceLevelID = i.ImportanceLevelID INNER JOIN
EmailAnchor AS e ON pc.ParentID=e.MailID
)

--SELECT [MailID],[Subject],[Body],[ParentID],[UserTo],[UserFrom],[UserID],[Trash],[PermDelete],[ImportanceLevel],[FromUserID],[ToUserID]
INSERT INTO @TempTab([MailID],[Subject],[Body],[ParentID],[UserTo],[UserFrom],[UserID],[Trash],[PermDelete],[ImportanceLevel],[FromUserID],[ToUserID])
SELECT [MailID],[Subject],[Body],[ParentID],[UserTo],[UserFrom],[UserID],[Trash],[PermDelete],[ImportanceLevel],[FromUserID],[ToUserID]
FROM EmailAnchor
WHERE ToUserID=@UserID

SELECT DISTINCT [MailID],[Subject],CAST([Body] AS NVARCHAR(MAX)) AS Body,[ParentID],[UserTo],[UserFrom],[UserID],[Trash],[PermDelete],[ImportanceLevel],[FromUserID],[ToUserID] FROM @TempTab WHERE [ParentID] = @ParentID

END

Essentially I want to pass the userid to show whos email should be displayed and of course the parentid of the email that is being displayed.

in return I need to have all the emails that are childs of that one parentid

Can someone help me with this one?
if needed I can send a script of the entire DB

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources