Hi. 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] [Timestamp] [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()),
[Timestamp] [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,
[Timestamp] [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.