locked
Generic Comments Module (Database Design and Implementation) RRS feed

  • 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'

    Wednesday, February 10, 2010 11:55 AM

Answers

  • User-389939489 posted

    Your design no. 2 looks proper to me, for the very reasons you mention (comments are comments, still we need referential integrity and consistency): have you got any specific doubt about it? Might be interesting to hear/read what the guys at SqlCentral were questioning.

    -LV

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 13, 2010 8:18 AM