Best practice for a "comment" table in a relational database [closed]

Question

Assume you want to build a database for some web application. This database already contains many tables and you might have to extend it in the future.

Also, you want the end user to be able to comment any kind of object in the database.

I would like to find a solution for this would be generic enough so that I don't have to extend it each time I add a new table in the database.

I thought of the following:

Table Name: comment

columns:

• id : the id of a comment
• user_id : the id of the user making the comment
• object_table_name : the table where the commented object is
• object_id : the id of the commented object in the object_table_name table.
• text : the text
• date : the date

This table sort of solve my problem, the only thing that troubles me is that the relational aspect of it is rather weak (I can't make object_id a foreign key for instance). Also if some day I need to rename a table I will have to change all the concerned entries in the comment table.

What do you think of this solution ? Is there design pattern that would help me out ?

Thanks.-

Answer 1

Isn't that cleaner ?

table comment_set

• id

table comment

• id
• comment_set_id -> foreign key to comment_set
• user_id
• date
• text

existing table foo

• ...
• comment_set_id -> foreign key to comment_set

existing table bar

• ...
• comment_set_id -> foreign key to comment_set

Answer 2

You are mixing data and metadata, which is not the best design pattern. They should be separated.

However, since the comments don't seem to be very important anyway, you solution is OK. The worst thing you can end up with is to lose comments on your objects.

Some databases, most notably, PostgreSQL, support COMMENT clause just for the cases like this.

Update:

If you want to comment on individual records in each table, it's OK to have such a table.

object_table_name does not have to change if you rename a table, since it's data, not metadata.

You cannot write a native SQL query that will fetch comments for the record of any table (not known by the moment of the query development), though you can build the dynamic queries to do that.

In this case, you will have to keep your data and metadata in sync (UPDATE the comment table when you RENAME the table they refer to). The first one is a DML statement (changes data), the second one is DDL (changes metadata).

Also make sure that all PRIMARY KEYs have the same types (same as object_id).