Generic Comments Module (Database Design and Implementation)

Question

User1100692814 posted

Hi all

I am in the process of redesigning our main product, a knowledge database system for clients to access clinical and specialist advice articles. There is a requirement to add "Social Networking" to this allowing users to connect and post remarks etc...

This is somewhat similar to the Facebook wall scenario - where Articles, Photos, Groups, Products, Events, ActivityFeed can all have comments. I am at a lose as to the database design I would need to implement this. All comments must allow for Moderation/Approval/Spam Reporting etc...

I originally thought a individual table for each Comments entity ex: ArticleComment, ProductComment would work as the comments themselves are not all directly related. Each table having the same exact columns. But this makes life abit awkward as then this creates a separation between something that for all purposes is the same i.e. They are all comments.

The other design idea was to have a single Comment table with an "arc" type relationship to related tables with FK references back to the source.

The other option was to have a single Comment table with an Object Type and XID but this breaks the rules of relation and does not all for referential integrity.

I am at a lose and don't know what to do. I have asked over at SQLCentral.com and all I have done is raised more questions than answers about what to do. Any advice or guidance would be great thanks in advance.

Regards

David

Design 1: This would require exact copies of this table for all the entities.

-- Photo Comments --
CREATE TABLE dbo.PhotoComment
(
    CommentID int IDENTITY(1,1) NOT NULL,
    PhotoID int NOT NULL,
    Body ntext NOT NULL,
    ReportedAsSpam bit NOT NULL CONSTRAINT [DF_PhotoComment_ReportedAsSpam] DEFAULT (0),
    IsSpam bit NOT NULL CONSTRAINT [DF_PhotoComment_IsSpam] DEFAULT (0),
    IsApproved bit NOT NULL CONSTRAINT [DF_PhotoComment_IsApproved] DEFAULT (1),
    CreatedDate datetime NOT NULL,
    CreatedBy uniqueidentifier NOT NULL,
    UpdatedDate datetime NOT NULL,
    UpdatedBy uniqueidentifier NOT NULL,
    IsDeleted bit NOT NULL CONSTRAINT [DF_PhotoComment_IsDeleted] DEFAULT (0),
                    
    CONSTRAINT [PK_PhotoComment] PRIMARY KEY (CommentID),
    CONSTRAINT [FK_PhotoComment_PhotoID] FOREIGN KEY (PhotoID) REFERENCES dbo.Photo(PhotoID),
    CONSTRAINT [FK_PhotoComment_CreatedBy] FOREIGN KEY (CreatedBy) REFERENCES dbo.aspnet_Users(UserId),
    CONSTRAINT [FK_PhotoComment_UpdatedBy] FOREIGN KEY (UpdatedBy) REFERENCES dbo.aspnet_Users(UserId)
)

Design 2: The "Arc" relationship

CREATE TABLE dbo.Comment
(
    CommentID int IDENTITY(1,1) NOT NULL,
    CommentType char(1) NOT NULL CHECK(CommentType IN ('A','I','E','G','U','P')),
    Body ntext NOT NULL,
    ReportedAsSpam bit NOT NULL CONSTRAINT [DF_Comment_ReportedAsSpam] DEFAULT (0),
    IsSpam bit NOT NULL CONSTRAINT [DF_Comment_IsSpam] DEFAULT (0),
    IsApproved bit NOT NULL CONSTRAINT [DF_Comment_IsApproved] DEFAULT (1),
    CreatedDate datetime NOT NULL,
    CreatedBy uniqueidentifier NOT NULL,
    UpdatedDate datetime NOT NULL,
    UpdatedBy uniqueidentifier NOT NULL,
    IsDeleted bit NOT NULL CONSTRAINT [DF_Comment_IsDeleted] DEFAULT (0),
                    
    CONSTRAINT [PK_Comment] PRIMARY KEY (CommentID),
    CONSTRAINT [FK_Comment_CreatedBy] FOREIGN KEY (CreatedBy) REFERENCES dbo.aspnet_Users(UserId),
    CONSTRAINT [FK_Comment_UpdatedBy] FOREIGN KEY (UpdatedBy) REFERENCES dbo.aspnet_Users(UserId),
    CONSTRAINT [UC_Comment_CommentID_CommentType] UNIQUE (CommentID, CommentType)
)

-- Article Comments --
CREATE TABLE dbo.ArticleComment
(
    ArticleID int NOT NULL,
    CommentID int NOT NULL,
    CommentType char(1) CONSTRAINT [DF_ArticleComment_CommentType] DEFAULT 'A', CHECK (CommentType = 'A'),
    
    CONSTRAINT [PK_ArticleComment] PRIMARY KEY CLUSTERED (ArticleID, CommentID),
    CONSTRAINT [FK_ArticleComment_ArticleID] FOREIGN KEY (ArticleID) REFERENCES dbo.Article(ArticleID),
    CONSTRAINT [FK_ArticleComment_CommentID] FOREIGN KEY (CommentID) REFERENCES dbo.Comment(CommentID),
    CONSTRAINT [FK_ArticleComment_CommentID_CommentType] FOREIGN KEY (CommentID, CommentType) REFERENCES dbo.Comment(CommentID, CommentType)
)

Design 3: The one that breaks the rules

CREATE TABLE dbo.Comment
(
    CommentID int IDENTITY(1,1) NOT NULL,
    ObjectType char(1) NOT NULL, /* A = Article, P = Photo etc... */
    XID int NOT NULL, /* Would be the ID of the main entity being queried. */
    Body ntext NOT NULL,
    ReportedAsSpam bit NOT NULL CONSTRAINT [DF_Comment_ReportedAsSpam] DEFAULT (0),
    IsSpam bit NOT NULL CONSTRAINT [DF_Comment_IsSpam] DEFAULT (0),
    IsApproved bit NOT NULL CONSTRAINT [DF_Comment_IsApproved] DEFAULT (1),
    CreatedDate datetime NOT NULL,
    CreatedBy uniqueidentifier NOT NULL,
    UpdatedDate datetime NOT NULL,
    UpdatedBy uniqueidentifier NOT NULL,
    IsDeleted bit NOT NULL CONSTRAINT [DF_Comment_IsDeleted] DEFAULT (0),
                    
    CONSTRAINT [PK_Comment] PRIMARY KEY (CommentID),
    CONSTRAINT [FK_Comment_CreatedBy] FOREIGN KEY (CreatedBy) REFERENCES dbo.aspnet_Users(UserId),
    CONSTRAINT [FK_Comment_UpdatedBy] FOREIGN KEY (UpdatedBy) REFERENCES dbo.aspnet_Users(UserId),
    CONSTRAINT [UC_Comment_CommentID_ObjectType_XID] UNIQUE (CommentID, ObjectType, XID)
)

-- Ex: Get all comments for article ID 23.
SELECT * FROM Comment Where ObjectType ='A' and XID = '23'