SQL Server 2005: Basic Insert / Record Logic Help Needed

I am designing a social networking site that has a "wall" feature like the others out there today. The database has an alerts table that stores some user action worthy of sharing with his friends. For example, when a user updates his status, all of his friends are notified. The below table shows two status updates from two unique users. The first (AlertId 689 and 690) is submitted by AccountId 53. Since he has one frinend - AccountId 57 - that row is added to the table so when this user logs on, he will see Account 53's update on his wall. In the same manner, the other user's status update has four rows because he has three friends.

[AlertId]  [AccountId]  [CreateDate] 1673397312    [AlertTypeId] [IsHidden] [Body] 

689        57       2010-08-10   0x0000000000018725      10          0    HTML
690        53       2010-08-10   0x0000000000018726      10          0    HTML

691        53       2010-08-10   0x000000000001872B      10          0    HTML
692        52       2010-08-10   0x000000000001872C      10          0    HTML
693        51       2010-08-10   0x000000000001872D      10          0    HTML
694        57       2010-08-10   0x000000000001872E      10          0    HTML

Now, a user can comment on any given item, in this case a statusupdate. When AddComment is submitted, we are using ObjectRecordId (which is the primary key of the alert being commented on) in order to identify which statusupdate is being commented on (fyi - the objectId tells us its a statusupdate):

public void AddComment(string comment)
    {
        if (_webContext != null)
        {
            var c = new Comment
                        {
                            Body = comment,
                            CommentByAccountId = _webContext.CurrentUser.AccountId,
                         开发者_开发问答   CommentByUserName = _webContext.CurrentUser.UserName,
                            CreateDate = DateTime.Now,
                            SystemObjectId = _view.ObjectId,
                            SystemObjectRecordId = _view.ObjectRecordId
                        };
            _commentRepository.SaveComment(c);
        }
        _view.ClearComments();
        LoadComments();
    }

Now, the problem is that when a user wants to comment on a friend's status update, he will be using the AlertId (or ObjectRecordId in the Comments table) corresponding to his account in the alerts table. The result is that comments are only viewable by the commenter and none of his friends:

[CommentId]  [Body]  [CommentById] [CommentByName] [ObjectId] [ObjectRecordId] [Delete]
   97     hello world.    57          GrumpyCat        7           690           0

Of course the solution to this is to do something similar to what I did in the alerts table - when somebody makes a comment, make a corresponding row for every friend in the comments table. But how do I access the AlertIds of all of my friend's status updates in the Alerts table and map them to the ObjectRecordId column in the comments table? Since I can only access the status updates corresponding to my account (and their corresponding alertids), I don't know what the alertids are for the same statusupdate in my friend's accounts.

The only solution that I can think of right now is stuffing the hidden field with all of my friend's corresponding alertIds so when I comment on an item, i already know what they are. But this feels sloppy and I'd like to know if there are any better ideas out there?

For what it is worth, here is the CREATE TABLE of dbo.Alerts:

CREATE TABLE [dbo].[Alerts](
    [AlertId] [bigint] IDENTITY(1,1) NOT NULL,
    [AccountId] [int] NOT NULL,
    [CreateDate] [datetime] NOT NULL CONSTRAINT [DF_Alerts_CreateDate]  DEFAULT (getdate()),
    1673397312 [timestamp] NOT NULL,
    [AlertTypeId] [int] NOT NULL,
    [IsHidden] [bit] NOT NULL CONSTRAINT [DF_Alerts_IsHidden]  DEFAULT ((0)),
    [Message] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_Alerts] PRIMARY KEY CLUSTERED 
(
    [AlertId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

And, here is dbo.Comments:

CREATE TABLE [dbo].[Comments](
    [CommentId] [bigint] IDENTITY(1,1) NOT NULL,
    1673397312 [timestamp] NOT NULL,
    [Body] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [CreateDate] [smalldatetime] NOT NULL,
    [CommentByAccountId] [int] NOT NULL,
    [CommentByUserName] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [SystemObjectId] [int] NOT NULL,
    [SystemObjectRecordId] [bigint] NOT NULL,
    [FlaggedForDelete] [bit] NOT NULL CONSTRAINT [DF_Comments_FlaggedForDelete]  DEFAULT ((0)),
 CONSTRAINT [PK_Comments] PRIMARY KEY CLUSTERED 
(
    [CommentId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I am using SQL Server 2005. Thanks in advance.

Update

I have some real concerns about your design, I've layed them out using scenarios. I named one of my concerns earlier, which is that I don't see any way of tieing an alert back to a comment.

Scenario: A friend posts on his wall saying "hey, i'm giving away my old computer, let me know if you want it". Of course you weren't able to access the site for two weeks due to some good reason. Now when you finally get back on and see the alert for your friends posting you want to go check it out, BUT! there is nothing tieing this alert back to a comment. So when you click it you just go to your friends wall and not stright to the posting. You should be able to click an alert and go straight to the comment/post but I don't see any way of doing this right now.

Secondly, I don't see anyway of replying to a comment.

Scenario: I go to friend X's page and see that he's in texas this week for business, and I want to comment on that. So I write in the text box "hey, bring me back a present" and submit it. Now what happens to this comment? It goes in the comments table with a comment ID and it has my ID attached to it, but where does anything in the database say that it is a reply to a comment?

I think if you solve some of these other design issues the issue will probably fix itself, or if I'm way off or there are other tables in the picture that aren't included let me know.

Original Post

It looks like you need an extra column in the Alerts table, at least as far as I can tell. Here is the question I asked myself: How do I tell, just by looking at any record in the Alerts table, what comment it belongs to? I can't as far as I know. This means that the Alert is very general "hey this user said something, but I don't know what and if he removes his comment this little alert will still be here because it's not attached...".

So, I think you need a column in the Alerts table that links it back to the original comment/posting/whatever. Now you can use that original "CommentID" (?) to make the posting and everything works out clean and pretty.

I know I didn't directly answer your actual question... but I think your table design might need some work.