Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2006-05-23 : 02:57:25
|
| Hi,I've run into a problem with designing a certain part of my community web application. Currently members can use the systems internal mail to mail other members and an email is immediately sent to notify the recipient member providing they have notifications turned on. The problem happens when certain more active members can receive more notifications than desired. (12 new messages in a day = 12 emails) I need to create a better system where I can run some code every 12 hours or so and make sure the person only gets 1 email. I started designing it by creating a table in which I just inserted a row each time a message was sent instead of firing out an email. The problem I have now run into is that I don't really have a way to relate the message notification with the actual message (messageID column), something I didnt really think I would need at the beginning (lack of planning =[ ). The reason this is a problem is because I don't want users who login and check their mail to still receive notifications. I could however use the users last login time as a solution and assume that a login equals all messages have been checked. Is this technique flawed ? I'm thinking maybe I should not even have a logging table, but perhaps just a query that can determine the same thing from the message table. This should be possible I believe, havent attempted. Writing something like this I'm note sure I would even know where to start :)Any opinions on this from you guys? I'd love to know what you think, and from there I can post some more info depending on which route we take and what info can be helpful. I think I've included enough to get a decent understanding of what I am trying to accomplish. If theres anything else I can post to help clear myself up, please let me know!Thanks very much once again sqlteam crew!!mike123CREATE TABLE [dbo].[Message] ( [MessageID] [int] IDENTITY (1, 1) NOT NULL , [MessageFrom] [int] NOT NULL , [MessageTo] [int] NOT NULL , [Message] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [prevMessage] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Subject] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [date] [smalldatetime] NULL , [Checked] [tinyint] NULL , [deletedbySender] [int] NULL , [deletedbyRecipient] [int] NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[EmailNotifications_NewMessages] ( [emailID] [int] IDENTITY (1, 1) NOT NULL , [from_userID] [int] NOT NULL , [to_userID] [int] NOT NULL , [messageTypeID] [int] NOT NULL , [dateSent] [datetime] NOT NULL , [deleted] [int] NOT NULL ) ON [PRIMARY]GO |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2006-05-24 : 20:42:15
|
| is my explanation too complicated? lol if so let me know I can maybe redo it .... bump in case anyone has any inputthanks,mike123 |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-24 : 23:14:42
|
| my guess your notification is via a trigger for every insert to your message table?you can try a job that runs once a day to check for messages and sends out the notification like...12 new messages--------------------keeping it simple... |
|
|
|
|
|
|
|
|
help with application design - 2 choices ?