Data model for a web messaging application.
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 theuser "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