Data model for a web messaging application.

From: Itai <itaitai2003_at_yahoo.com>
Date: 3 Oct 2004 01:33:05 -0700
Message-ID: <429f6e7d.0410030033.7573337c@posting.google.com>

I need to develop an internal messaging sub-system that is similar to a web mail application but without SMTP support (e.g message routes are confined to the webapp domain). The requirements are rather simple: Each user (e.g mailbox) can view incoming messages and his outgoing messages. Message quota is defined as the sum of all incoming and outgoing messages per user
and tracked in the users' row (Users table – log_TotalMessages). The quota is enforced by the business logic layer and not by the DB.

I am considering the following data model for the storage component, and would appreciate community feedback:

Table layout for incoming and outgoing messages

---

CREATE TABLE [dbo].[Messages] (
[MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID

[RecipientID] [int] NOT NULL ,                 // The userid ('Users'
Table)
[SenderID] [int] NOT NULL ,                    // The userid ('Users'
Table)
[GroupID] [uniqueidentifier] NULL ,            // Only assigned if the

user "replyed" to an incoming message

[SubmitDate] [smalldatetime] NOT NULL , // the date of the message

[DeleteBySender] [bit] NOT NULL , // Since I want to maintain only one copy of each message I mark a message "to be deleted" and delete only if both are true.

[DeleteByRecipient] [bit] NOT NULL ,  

[SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread messages

[Subject] [tinyint] NOT NULL , // Subject is derived from a fixed list

[MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] CREATE INDEX [Messages_RecipientID_IDX] ON [dbo].[Messages]([RecipientID]) ON [PRIMARY] CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID]) ON [PRIMARY] /* Send Message */

CREATE PROCEDURE SendMessage (

@IN_RecipientID int,
@IN_SenderID int,
@IN_GroupID uniqueidentifier,
@IN_Subject tinyint,
@IN_MessageText varchar(2000),

@OUT_ERRCODE tinyint OUTPUT

)

AS
BEGIN TRANSACTION SendMessageTrans

  INSERT INTO Messages

             
             (RecipientID, 
              SenderID, 
              GroupID, 
              SubmitDate,
  	      Subject, 
              MessageText)
       
      VALUES (@IN_RecipientID, 
              @IN_SenderID, 
              @IN_GroupID, 
              GETDate(),
	      @IN_Subject, 
              @IN_MessageText) 


    UPDATE Users 
         
         SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
         WHERE usr_AccountNo = @IN_RecipientID

    UPDATE Users 
         
         SET log_TotalMessages = log_TotalMessages + 1
         WHERE usr_AccountNo = @IN_SenderID

SAVE TRANSACTION SendMessageTrans

SET @OUT_ERRCODE = @@error

IF (@@error <> 0)

BEGIN
  ROLLBACK TRANSACTION SendMessageTrans
END ELSE BEGIN
  COMMIT TRANSACTION SendMessageTrans
END /* ReadMessage */

CREATE PROCEDURE ReadMessage (

@IN_MessageID int,
@IN_RecipientID int,

@OUT_ERRCODE tinyint OUTPUT

)

AS

BEGIN TRANSACTION ReadMessageTrans

  SELECT MessageText FROM Messages WHERE MessageID = @IN_MessageID

  UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =
@IN_MessageID

  UPDATE Users SET log_NumberOfNewMessages = log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @IN_RecipientID

SAVE TRANSACTION ReadMessageTrans

SET @OUT_ERRCODE = @@error

IF (@@error <> 0)
BEGIN
  ROLLBACK TRANSACTION ReadMessageTrans
END ELSE BEGIN
  COMMIT TRANSACTION ReadMessageTrans
END /* Delete Message */

CREATE PROCEDURE DeleteMessage (

@IN_MessageID int,
@IN_DeleteIncomingMessage bit,
@IN_DeleteOutgoingMessage bit,

@OUT_ERRCODE tinyint OUTPUT

)

AS
BEGIN TRANSACTION DeleteMessageTrans

DECLARE @Recipient int
DECLARE @Sender int

SET @Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =
@IN_MessageID)

SET @Sender = (SELECT SenderID FROM Messages WHERE MessageID =
@IN_MessageID)

IF (@IN_DeleteIncomingMessage = 1)

BEGIN      IF((SELECT DeleteBySender FROM Messages WHERE MessageID =
@IN_MessageID) = 1)

     
        BEGIN
           DELETE FROM Messages WHERE MessageID = @IN_MessageID      
           UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @Recipient
        END
     
     ELSE

        BEGIN
      
           UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =


@IN_MessageID

           UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @Recipient
        END

END IF (@IN_DeleteOutgoingMessage = 1)

BEGIN      IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =
@IN_MessageID) = 1)

     
        BEGIN
           DELETE FROM Messages WHERE MessageID = @IN_MessageID  
           UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @Sender
        END

     ELSE

        BEGIN
           UPDATE Messages SET DeleteBySender = 1 WHERE MessageID =


@IN_MessageID

           UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @Sender
        END

END SAVE TRANSACTION DeleteMessageTrans

SET @OUT_ERRCODE = @@error
IF (@@error <> 0)
BEGIN
  ROLLBACK TRANSACTION DeleteMessageTrans END
ELSE
BEGIN
  COMMIT TRANSACTION DeleteMessageTrans
END /* ListIncomingMessages */

CREATE PROCEDURE ListIncomingMessages (

@IN_RecipientID int

)

AS

SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID = @IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC

/* ListOutgoingMessages */

CREATE PROCEDURE ListOutgoingMessages (

@IN_SenderID int

)

AS

SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID = @IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC

Thanks in advance!

-Itai Received on Sun Oct 03 2004 - 03:33:05 CDT