The best way is to handle this is to create a rich join table.
Ie:
| has many => | | <= has many |
Person | | Credit | | Movie
| <= belongs to | | belongs to => |
Where Person and Movie don t change much from the initial example. And Credit contains more fields than just person_id, and movie_id. The extra fields for Credit would be role, and character.
Then it s just a has many through relationship. However we can add extra associations to get more detail. Keeping with the movies example:
class Person < ActiveRecord::Base
has_many :credits
has_many :movies, :through => :credits, :unique => true
has_many :acting_credits, :class_name => "Credit",
:condition => "role = Actor "
has_many :acting_projects, :class_name => "Movie",
:through => :acting_credits
has_many :writing_credits, :class_name => "Credit",
:condition => "role = Writer "
has_many :writing_projects, :class_name => "Movie",
:through => :writing_credits
end
class Credit < ActiveRecord::Base
belongs_to :person
belongs_to :movie
end
class Movie < ActiveRecord::Base
has_many :credits
has_many :people, :through => :credits, :unique => true
has_many :acting_credits, :class_name => "Credit",
:condition => "role = Actor "
has_many :actors, :class_name => "Person", :through => :acting_credits
has_many :writing_credits, :class_name => "Credit",
:condition => "role = Writer "
has_many :writers, :class_name => "Person", :through => :writing_credits
end
With all those extra associations. Each of the following is only one SQL query:
@movie.actors # => People who acted in @movie
@movie.writers # => People who wrote the script for @movie
@person.movies # => All Movies @person was involved with
@person.acting_projects # => All Movies that @person acted in
