Have Geek Brain

Best Comment Table Solution for Multiple Content Types

I am currently creating a comment table for a site I am creating. Users will be able to upload images, link videos and add audio files to the profile.

Each of these types of content must be commented. Now I am wondering what is the best approach to this.

My current parameters:

  • have one big comment table and link tables for each content type (comments_videos, ...) with comments and _id.

  • so that comments are separated by the content type for which they are intended. Thus, each content type will have its own comment table with comments for that type.

+2


source to share


4 answers


For just one comment in the file, make one 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 other tables it is used like this:

Video
VideoID
Video...
CommentID  FK

Audio
AudioID
Audio...
CommentID  FK


For multiple comments per file, create one 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 other tables it is used 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...
+3


source


I disagree with the accepted answer. How would you check data integrity in this way? you can have corrupt IDs if your application is not doing the right thing. In addition, you should never rely solely on the application for data integrity. Also have you thought about how to cascade your deletions into a comment table when deleting any media?

If you want to keep all comments in one table, you will need two additional columns: one for the media ID and one for the media type. Then you can write a trigger to ensure the integrity of your media (id, type).



Can anyone say better? Ken

+4


source


Personally, I can go for the first option. Have all comments in one comment type table, which will determine the comment type.

This will allow you to download comments regardless of the actual content, then use something like display content (video / adiou) that can be downloaded on demand.

It also makes it easier for you to request comments on a post.

+2


source


To be honest, neither of these two options really matter. You would expect a given piece of content of any type (audio / video / etc.) to 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 not be displayed on both the video and the picture). As long as this is true, there isn't much difference here, just minor details.

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

+1


source






More articles:


All Articles