Re: Data model for a web messaging application.

From: Uri Dimant <urid_at_iscar.co.il>
Date: Sun, 3 Oct 2004 11:47:38 +0200
Message-ID: <#7$Ka4SqEHA.3396_at_tk2msftngp13.phx.gbl>


Itai,shalom
What is a primary key of the table? So what are you actually asking? Are you concerned about a perfomance of the stored procedures? Do they give you a wrong output?

"Itai" <itaitai2003_at_yahoo.com> wrote in message news: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 - 11:47:38 CEST

Original text of this message