Data Model For A Web Messaging Application.

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