comment system where comments may have parents that are also comments but may be practically any resource

Question

How do I design a comment system database where comments may have parents that are also comments but may be products, users, or practically any resource?

Current tables: tags, products, users, comments.

This is for a somewhat high traffic site, so I can't have it doing all kinds of craziness.

Answer 1

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.