Best solution for a comment table for multiple content types

Question

I'm currently designing a comments table for a site I'm building. Users will be able to upload images, link videos and add audio files to the profile.

Each of these types of content must be commentable. Now I'm wondering what's the best approach to this.

My current options are:

1. to have one big comments table and a link tables for every content type (comments_videos, ...) with comment_id and _id.

2. to have comments separated by the type of content their for. So each type of content would have his own comments table with the comments for that type.

Answer 1

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

Comments
CommentID     int identity/auto generate Primary key
CommentType   char(1) or tinyint/byte etc FK to CommentTypes table
Comment       string
CreateDate    date/time
CreateUserID  int  FK

in the other tables use it like this:

Video
VideoID
Video...
CommentID  FK

Audio
AudioID
Audio...
CommentID  FK

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

Comments
CommentID     int identity/auto generate Primary key
MediaID       int --no explicit FK, but can join to VideoID,AudioID etc on this
CommentType   char(1) or tinyint/byte etc FK to CommentTypes table
Comment       string
CreateDate    date/time
CreateUserID  int  FK

in the other tables use it like this:

Video
VideoID    int identity/auto generate Primary key, joins to Comments.MediaID
Video...


Audio
AudioID    int identity/auto generate Primary key joins to Comments.MediaID
Audio...

Answer 2

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.

Answer 3

Honestly, neither of those two options really matter. You are anticipating that a given piece of content, of any type (audio/video/etc), can have multiple comments. Each comment is exclusive to one piece of content. (EG: a comment can only be on one content_type/the same comment can't appear on both a video and a picture). As long as that is true, there is no big difference here, only minor details.

I would just go with having one content table. If you want, you can create views for each that show all comments for a given content_type.

Answer 4

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