I am presently creating a comments table for any site I am building. Customers will have the ability to upload images, link videos and add audio recordings towards the profile.

All these kinds of content should be commentable. Now I am wondering what's a great way for this.

My current choices are:

  1. to possess one large comments table along with a link tables for each content type (comments_videos, ...) with comment_id and _id.

  2. to possess comments separated by the kind of content their for. So each kind of content might have their own comments table using the comments for your type.

For just 1 comment per file create a single table such as 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

within the other tables utilize it such as this:

Video
VideoID
Video...
CommentID  FK

Audio
AudioID
Audio...
CommentID  FK

For multiple comments per file create a single table such as 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

within the other tables utilize it such as 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...

Personally I would choose the very first option. Have your comments ought to in one table, having a comment type, that will identify the kind of comment.

This could permit you to load your comments ought to in addition to the actual content, after which use something similar to a presentation content (video/adiou) which may be loaded when needed.

Also, it might permit you to simplify the query for comments regarding a publish.

Honestly, neither of individuals two options really matter. You're anticipating that the given bit of content, regardless of the sort (audio/video/etc), might have multiple comments. Each comment is different to 1 bit of content. (EG: a comment are only able to be on a single content_type/exactly the same comment can't show up on both a relevant video along with a picture). As lengthy as that's true, there's no large difference here, only minor particulars.

I'd go with getting one content table. If you would like, you may create sights for every that demonstrate all comments for any given content_type.

I disagree using the recognized answer. How does one look into the integrity from the data by doing this ? you could have corrupt ids in case your application isn't doing the best factor. Besides, you shouldn't depend only around the application to make sure the information integrity. Also, have you think how you can cascade your removes towards the comment table whenever you remove any media ?

Should you wish to keep all of the comments in a single table you'll need two extra posts, one for that media id and something for that media type. You'll be able to write a trigger to enforce the integrity of the media(id,type) combination.

Can anybody say better ? -ken