Creating a comment table

Question

Basically, I want to create a comment system in which comments can have parents who are also comments, but I would also like to have potential parents who can be something else, like users or products (i.e. E. I want to be able to comment on products, users, other comments, or almost any resource)

How can I do it?

Current Tables:

tags, products, users, comments

edit - this will be for somewhat high traffic, so I can not get it to do all kinds of crazy things :-)

Answer 1

Do you want to receive comments on products, users, reviews, etc.? Or find the products, users, reviews, etc. that the comment refers to?

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

create table join_products_comments ( product_id int (unique, ie, one thread of comments per product), comment_thread_id int ); create table join_users_comments ( user_id int (unique, ie, one thread of comments per user), comment_thread_id int ); 

Where comment_thread is just a link to a thread that refers to each comment:

 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 ); 

Thus, each remarkable object in the system will have a connection table and one comment_thread, waiting for users to add comments. Or you could just refer to the root comment instead and without this indirection.

Answer 2

It would be best to isolate comments from goals. Sort of...

 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) 

If only root comments (without parent) will have a string. This allows you to still maintain a strong foreign key architecture and still be able to associate a comment with one product.

Answer 3

may be

 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 save the table source (product, user, etc.), and in row_source, the identifier of the row pointed to by the comment.

Answer 4

my attempt:

 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 you to find them using the index, without unnecessary storage costs