Designing a comment table

Question

Basically I want to create a comment system where comments may have parents that are also comments BUT I would also like them to potentially have parents that may be something else, such as users or products (ie, I want to be able to comment on products, users, other comments, or practically any resource)

How should I do that?

Current tables:

tags, products, users, comments

edit - this would be for a somewhat high traffic site, so I can't have it doing all kinds of craziness :-)

Answer 1

maybe

CREATE TABLE comment (
id INT PK,
parent_comment INT NULL FK,
content TEXT,
table_source VARCHAR(30), -- SYSNAME,
row_source INT,
)

In table_source you would save the table source (product, user, etc), and in row_source, the id of the row the comment is pointing.

Answer 2

Your best bet would be isolating the comments from the targets. Something like...

comment:
    comment_id (PK),
    user_id (FK),
    date,
    comment,
    parent_comment_id (FK)

Then tables like...

product_comment:
    product_comment_id (PK),
    product_id (FK),
    comment_id (FK, unique)

Where only the root comments (no parent) would have a row. This would allow you to still maintain a strong foreign-key architecture all around and still only be able to associate a comment to one product.

Answer 3

my try:

CREATE TABLE Comment
(
    CommentID               INT            NOT NULL IDENTITY(1,1) PRIMARY KEY
   ,CommentValue            VARCHAR(5000)  NOT NULL
   ,CommentParentCommentID  INT            NULL     --fk to self
   ,CommentParentTagID      INT            NULL     --fk to Tags
   ,CommentParentProductID  INT            NULL     --fk to Parents
   ,CommentParentUserID     INT            NULL     --fk to Users

)

this will allow for you to find them using an index, without too much waste with storage

Answer 4

Do you want to have comments on products, users, reviews, etc? Or find the products, users, reviews, etc, that a comment is referring to?

For the former, I would have tables to associate things with their comments:

create table join_products_comments (
   product_id int (unique, i.e., one thread of comments per product),
   comment_thread_id int
);

create table join_users_comments (
   user_id int (unique, i.e., one thread of comments per user),
   comment_thread_id int
);

Where a comment_thread is just a reference to a thread that every comment references:

create table comment_threads (
    thread_id int (PK),
    thread_name nvarchar2(256),
    created datetime
);

create table comments (
    comment_id int (PK),
    comment_thread_id int (FK),
    parent_comment_id int (FK),
    user_id int (FK), -- person who posted the comment
    comment text,
    created datetime
);

So every commentable entity in the system would have a join table and one comment_thread just waiting for eager users to add comments to. Or you could just link to a root comment instead and do without that indirection.