Best solution for a comment table for multiple content types

I disagree with the accepted answer.How would you check the integrity of the data this way ? you can have corrupt ids if your application is not doing the right thing. Besides, you should never rely only on the application to assure the data integrity.Also, did you think how to cascade your deletes to the comment table when you remove any media ?

If you want to keep all the comments in one table you need two extra columns, one for the media id and one for the media type. Then you can write a trigger to enforce the integrity of your media(id,type) combination.

Can anyone say better ?-ken

For only 1 comment per file make a single table like this:

CommentsCommentID     int identity/auto generate Primary keyCommentType   char(1) or tinyint/byte etc FK to CommentTypes tableComment       stringCreateDate    date/timeCreateUserID  int  FK

in the other tables use it like this:

VideoVideoIDVideo...CommentID  FKAudioAudioIDAudio...CommentID  FK

For multiple comments per file make a single table like this:

CommentsCommentID     int identity/auto generate Primary keyMediaID       int --no explicit FK, but can join to VideoID,AudioID etc on thisCommentType   char(1) or tinyint/byte etc FK to CommentTypes tableComment       stringCreateDate    date/timeCreateUserID  int  FK

in the other tables use it like this:

VideoVideoID    int identity/auto generate Primary key, joins to Comments.MediaIDVideo...AudioAudioID    int identity/auto generate Primary key joins to Comments.MediaIDAudio...

Personally I might go for the first option. Have all the comments in a single table, with a comment type, that would identify the type of comment.

This would allow you to load the comments independent of the actual content, and then use something like a display content (video/adiou) which can be loaded on demand.

Also, it would allow you to simplify the query for comments regarding a post.