Multiple comment types

Question

User1100692814 posted

Hi all

I am trying to get some desgin questions sorted in my head.

I am working on a site that has multiple entities: Articles, Forums, Groups, Events, Store etc... A user can post comments for:

1. Articles

2. UserWall / User Comments

3. Groups

4. Events

5. Products

Should I (in the interests of best practice...) create a singular comments table with LinkTables for the relevant entity ex:

dbo.Comments(CommentID)

dbo.ArticleComments (ArticleID, CommentID)

dbo.ProductComments(ProductID, CommentID)

dbo.UserComments(UserID, CommentID)

OR

Is it better to have the WHOLE table strucutre replicated for ArticleComments, ProductComments, UserComments etc... ex (in short):

CREATE TABLE dbo.ArticleComments

(

  CommentID int IDENTITY(1,1) NOT NULL,

  ArticleID int NOT NULL REFERENCES Articles(ArticleID),

  FromID int NOT NULL,

  Body ntext NOT NULL,

  PostedDate datetime NOT NULL,

)

GO

Any pointers would be great. I am presuming replicate the whole table for each entity type (as pointed out to me recently) SQL is not an OOP language and trying to make it behave like one usually leads to problems, it's the problem of mixing apple and oranges.

Thanks in advance

Dave

Answer 1

User-1507865547 posted

Should I (in the interests of best practice...) create a singular comments table with LinkTables for the relevant entity ex:

dbo.Comments(CommentID)

dbo.ArticleComments (ArticleID, CommentID)

dbo.ProductComments(ProductID, CommentID)

dbo.UserComments(UserID, CommentID)

Not a bad approach. But, consider few points as mentioned below.

1) Your Comments table will gradually increase in size, which may become bottleneck at some point of time. 

2) I think the way you defined the tables is not related to OOP but they are the normalized one(which I prefer)

3) You may need to consider the archiving mechanism for the comments older than a specified time.

OR

Is it better to have the WHOLE table strucutre replicated for ArticleComments, ProductComments, UserComments etc... ex (in short):

CREATE TABLE dbo.ArticleComments

(

  CommentID int IDENTITY(1,1) NOT NULL,

  ArticleID int NOT NULL REFERENCES Articles(ArticleID),

  FromID int NOT NULL,

  Body ntext NOT NULL,

  PostedDate datetime NOT NULL,

)

GO

I will say this as the denormalized approach. Where all the fields related to the comments will be replicated in each of the tables. Now, consider in future you want to provide moderation for each of the comments. You may decide to add a field named IsModerated(bit) then you will have to add the field in each of the tables related to the Comment.

I, personally, prefer first approach by providing archiving mechanism(if possible). Of course, you are the best judge of which mechanism will suite you.

Hope, it will help.

Answer 2

User1100692814 posted

Hi NihirPorecha

Thanks for the reply. I prefer the first one as well (Normalisation Rules!?!)

For archiving, what would be the best way of doing this? Add a 2 extra columns: IsArchived, ArchivedDate and the filter?

I found this article is this a good approach? Thanks again

David

Answer 3

User-1507865547 posted

Hi David,

The article is really good. It does make sense as well(I, personally, never went into that much depth ). Archiving is a mechanism to replicate your table structure and store the historical data, which you can access at any give point of time.

For archiving, what would be the best way of doing this? Add a 2 extra columns: IsArchived, ArchivedDate and the filter?

This doesnt make sense as it will not help you in decreasing your actual Comments table.

By the way, while reading the article, I realized that you can use Partitioning mechanism available in SQL server for this kind of scenario. I do not have any good article which can help you for partitioning stuff but google or bing is the great place to search 

Answer 4

User1100692814 posted

Hi again - I need some comments and thoughts from you all...

I agree that the normalised approach is the way to go but I need to make life easy for maintenance purposes. I want to avoid multiple pages that manage comments ex. ManageArticleComments.aspx, ManageEventComments.aspx, ManagePhotoComments.aspx...so

2 Options spring to mind

1.

I was going to include in the Comments table 2 additional columns: ObjectTypeID, XID. ObjectTypeID is a FK to ObjectType (Article, Event, Groups etc...) and XID is an int datatype.

That way on the ManageComments.aspx page when I list the Comments, I can include a "Related To" column, which would feature a link to the respective Article or Event or Photo.

or

2.

List all comments on ManageComments.aspx with a Filter dropdownlist ex: All Comments, Article, Event, Photo etc... Once changed I programmatically rebind the list of comments for the specific type. i.e.

if (DropDownList1.SelectedValue == "Article")

{

      lvwComments.DataSource = Articles.GetComments();

}

This would be handled better in another function or switch statement but you get the idea.

Thanks in advance.

Dave