Re: Data model for a web messaging application.

From: John Bell <jbellnewsposts_at_hotmail.com>
Date: Sun, 3 Oct 2004 20:31:49 +0100
Message-ID: <41605341$0$21630$afc38c87_at_news.easynet.co.uk>


Hi

I am not sure what you are requiring people to do regarding your post, you are the only person that can do the analysis of what is required, if you have captured them correctly then you will know what to store in the database. That said, there does not seem to be any referential integrity built into the DDL, FKs and PKs should be defined. There is probably a natural key of RecipientID, SenderID, and SubmitDate so a covering unique index may be useful, and a INT may not be sufficient for you messageid. Whether just storing bits to indicated the actions or whether a date would be better would depend on whatever auditing requirements you require.

In your stored procedures you should implement better error handling, any statement may fail and you are only checking the result from a few. From Books online "Because _at__at_ERROR is cleared and reset on each statement executed, check it immediately following the statement validated, or save it to a local variable that can be checked later." you may not even be checking what you think!.

John

"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 - 21:31:49 CEST

Original text of this message