How do I organize/store a "type" field for a comments table?

Question

My application has about half a dozen different types of items that a user can comment on (articles, photos, videos, user profiles, blog posts, forum posts).

My plan right now is to have a single comments table in my database and then have parent_id and type fields in the table.

The type field would just be a string and the contents of it would be the name of it's parent table. So for an Article comments, the type would be article, for example.

Is that the best way to handle the comments table? Or is there some other, more efficient way to do that?

Answer 1

If a comment can't apply to multiple things (the same comment can't apply to both an article and a blog post, or two different articles) then why is it a base entity?

If you're committed to it, I'd have a comment table that looked like this:

COMMENT_ID
COMMENT_BODY
USER_ID
DATE
ARTICLE_ID references ARTICLE on delete cascade
BLOG_POST_ID references BLOG_POST on delete cascade
... etc

And then have constraint that says one and only one of the parents can apply.

An alternative is to have a COMMENT table for each base entity, so you'd have ARTICLE_COMMENTS, BLOG_POST_COMMENTS, and so forth.

Answer 2

What you are describing is a called a polymorphic association. Rails can handle that out of the box, see this episode: http://railscasts.com/episodes/154-polymorphic-association

Answer 3

Using a polymorphic association would be the best way to achieve this - check out Ryan's railscast on the subject (or the ASCIIcast at http://asciicasts.com/episodes/154-polymorphic-association). I think you'll end up with something like:

class Comment < ActiveRecord::Base

  belongs_to :commentable, :polymorphic => true
  belongs_to :user

end

Then each model you want users to be able to comment on will have the line:

has_many :comments, :as => :commentable

I also found this post useful when setting up polymorphic comments. Hope that helps!

Answer 4

I'd suggest looking at plugins like acts_as_commentable, and looking at how they are implemented. Or just use the plugin.

Answer 5

I think your design is pretty good. It is simple and easy to implement. The only requirement would be that the data-types of the row identifiers would have to be the same as comment.parent_id so that your joins are consistent. I would actually define views on the comments table for each 'type'. For example, "create photo_comments as select * from comments where type = 'PHOTOS'". then you could join from PHOTOS to PHOTO_COMMENTS on PHOTOS.PHOTO_ID = PHOTO_COMMENTS.PARENT_ID, and so on.

You could also create a supertype say, widget, and each of your photo, blog_post etc. could be sub-types of widget. Then you could constrain your comments table to have an FK to the widget table. The 'type' column could then be moved to the widget table.