Associating notes with different entities in a database

Question

At my job, we have judges perform a variety of tasks, e.g., rate movies or compare two pieces of text.

We're in the process of designing a new database to hold all our data (we have some data already, but the database it's in is pretty hack), and I'm starting to build a Rails analytics application that will serve as a dashboard on these judgments. Tables will include things like Judges, Movies, Text, MovieRatings, TextComparisons.

As part of the application, we want to be able to add comments or flag items from these tables. For example, someone might want to add a comment to Judge 1 saying "This judge is very inconsistent" and add a comment to Rating 2 saying "This rating is unexpected", or flag different types of movies or texts for review.

What is the best way to handle adding comments or flags to the database? For example, do we want to create a new Comment table for each entity (add a JudgesComments, MoviesComments, TextComments, etc.)? Or do we want to have a single Comments table with (id, comment) columns [which, I guess, would require ids throughout the database to be globally unique within the database, instead of unique only within its table]?

Answer 1

Answer 2

I have worked in a system where there was a single Comments table, and a globally unique ID for each record in each table you could have a comment for. The system worked fine, wasn't hard to maintain, and it was easy for new people to see how it worked. Generating new records in the comment-able tables was slow by computer standards, but it wasn't really an issue for the users of the system.

Answer 3

You should use polymorphic associations, so that you will have a single Comment model and controller. According to the excellent #154 "Polymorphic Association" Railscast, after adding a commentable_type:string and commentable_id:integer to your comments table, your code should look something like this:

# app/models/comment.rb
class Comment < ActiveRecord::Base
  belongs_to :commentable, :polymorphic => true
end

# app/models/judge.rb
class Judge < ActiveRecord::Base
  has_many :comments, :as => :commentable
end

# app/models/movie.rb
class Movie < ActiveRecord::Base
  has_many :comments, :as => :commentable
end

# app/models/text.rb
class Text < ActiveRecord::Base
  has_many :comments, :as => :commentable
end

# app/controllers/comments_controller.rb
def index
  @commentable = find_commentable
  @comments = @commentable.comments
end

def create
  @commentable = find_commentable
  @comment = @commentable.comments.build(params[:comment])
  if @comment.save
    flash[:notice] = "Successfully created comment."
    redirect_to :id => nil
  else
    render :action => 'new'
  end
end

private

def find_commentable
  params.each do |name, value|
    if name =~ /(.+)_id$/
      return $1.classify.constantize.find(value)
    end
  end
  nil
end

in the routes:

# config/routes.rb
map.resources :judges, :has_many => :comments
map.resources :movies, :has_many => :comments
map.resources :texts, :has_many => :comments

and in the view:

<!-- app/views/comments/index.html.erb -->

<div id="comments">
<% for comment in @comments %>
  <div class="comment">
    <%=simple_format comment.content %>
  </div>
<% end %>
</div>

<h2>New Comment</h2>
<%= form_for [@commentable, Comment.new] do |f| %>
  <p>
    <%= f.label :content %><br />
    <%= f.text_area :content %>
  </p>
  <p><%= f.submit "Submit" %></p>
<% end %>