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_at_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 (

_at_IN_RecipientID int,
_at_IN_SenderID int,
_at_IN_GroupID uniqueidentifier,
_at_IN_Subject tinyint,
_at_IN_MessageText varchar(2000),

_at_OUT_ERRCODE tinyint OUTPUT

)

AS
BEGIN TRANSACTION SendMessageTrans

  INSERT INTO Messages

             
             (RecipientID, 
              SenderID, 
              GroupID, 
              SubmitDate,
  	      Subject, 
              MessageText)
       
      VALUES (_at_IN_RecipientID, 
              _at_IN_SenderID, 
              _at_IN_GroupID, 
              GETDate(),
	      _at_IN_Subject, 
              _at_IN_MessageText) 


    UPDATE Users 
         
         SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
         WHERE usr_AccountNo = _at_IN_RecipientID

    UPDATE Users 
         
         SET log_TotalMessages = log_TotalMessages + 1
         WHERE usr_AccountNo = _at_IN_SenderID



SAVE TRANSACTION SendMessageTrans

SET _at_OUT_ERRCODE = @@error

IF (_at__at_error <> 0)

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

CREATE PROCEDURE ReadMessage (

_at_IN_MessageID int,
_at_IN_RecipientID int,

_at_OUT_ERRCODE tinyint OUTPUT

)

AS

BEGIN TRANSACTION ReadMessageTrans

  SELECT MessageText FROM Messages WHERE MessageID = _at_IN_MessageID

  UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =
_at_IN_MessageID

  UPDATE Users SET log_NumberOfNewMessages = log_NumberOfNewMessages - 1 WHERE usr_AccountNo = _at_IN_RecipientID

SAVE TRANSACTION ReadMessageTrans

SET _at_OUT_ERRCODE = @@error

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

CREATE PROCEDURE DeleteMessage (

_at_IN_MessageID int,
_at_IN_DeleteIncomingMessage bit,
_at_IN_DeleteOutgoingMessage bit,

_at_OUT_ERRCODE tinyint OUTPUT

)

AS
BEGIN TRANSACTION DeleteMessageTrans

DECLARE _at_Recipient int
DECLARE _at_Sender int

SET _at_Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =
_at_IN_MessageID)

SET _at_Sender = (SELECT SenderID FROM Messages WHERE MessageID =
_at_IN_MessageID)

IF (_at_IN_DeleteIncomingMessage = 1)

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

     
        BEGIN
           DELETE FROM Messages WHERE MessageID = _at_IN_MessageID      
           UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = _at_Recipient
        END
     
     ELSE

        BEGIN
      
           UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =

_at_IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1 WHERE usr_AccountNo = _at_Recipient END

END IF (_at_IN_DeleteOutgoingMessage = 1)

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

     
        BEGIN
           DELETE FROM Messages WHERE MessageID = _at_IN_MessageID  
           UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = _at_Sender
        END

     ELSE

        BEGIN
           UPDATE Messages SET DeleteBySender = 1 WHERE MessageID =

_at_IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1 WHERE usr_AccountNo = _at_Sender END

END SAVE TRANSACTION DeleteMessageTrans

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

CREATE PROCEDURE ListIncomingMessages (

_at_IN_RecipientID int

)

AS

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

/* ListOutgoingMessages */

CREATE PROCEDURE ListOutgoingMessages (

_at_IN_SenderID int

)

AS

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

Thanks in advance!

-Itai Received on Sun Oct 03 2004 - 10:33:05 CEST

Original text of this message