Active Record Associations: has_and_belongs_to_many, has_many :through or polymorphic association?

Question

The Ruby on Rails app I am working on allows users to create and share agendas with other users.

In addition, we must be able to:

• Display a list of agendas for each user, on his profile
• Display a list of users associated with an agenda, on the agenda's page
• When sharing an agenda with another user, define a role for this user, and display the role of this user on the list mentioned right above

I was going to go with a has_and_belongs_to_many association between the user and the agenda models, like that:

class User < ActiveRecord::Base
  has_and_belongs_to_many :agendas
end

class Agenda < ActiveRecord::Base
  has_and_belongs_to_many :users
end

But then I wondered whether this would let me get and display the @user.agenda.user.role list of roles on the given agenda page of a given user.

And I thought I should probably go with a has_many :through association instead, such as:

class User < ActiveRecord::Base
  has_many :roles
  has_many :agendas, through: :roles
end

class Role < ActiveRecord::Base
  belongs_to :user
  belongs_to :agenda
end

class Agenda < ActiveRecord::Base
  has_many :roles
  has_many :users, through: :roles
end

And although I was pretty comfortable about the idea of a user having several roles (one for each agenda), I am not sure about the idea of an agenda having several roles (one for each user?).

Finally, to add to the confusion, I read about the polymorphic association and thought it could also be a viable solution, if done this way for instance:

class Role < ActiveRecord::Base
  belongs_to :definition, polymorphic: true
end

class User < ActiveRecord::Base
  has_many :roles, as: :definition
end

class Agenda < ActiveRecord::Base
  has_many :roles, as: :definition
end

Does any of the above solutions sound right for the situation?

UPDATE: Doing some research, I stumbled upon this article (from 2012) explaining that has_many :through was a "smarter" choice than has_and_belongs_to_many. In my case, I am still not sure about the fact that an agenda would have many roles.

UPDATE 2: As suggested in the comments by @engineersmnkyn, a way of solving this would be to go with two join tables. I tried to implement the following code:

class User < ActiveRecord::Base
  has_many :agendas, through: :jointable
end

class Agenda < ActiveRecord::Base

end

class Role < ActiveRecord::Base

end

class Jointable < ActiveRecord::Base
  belongs_to :user
  belongs_to :agenda
  has_many :agendaroles through :jointable2
end

class Jointable2 < ActiveRecord::Base
  belongs_to :roles
  belongs_to :useragenda
end

I am not sure about the syntax though. Am I on the right track? And how should I define the Agenda and the Role models?

UPDATE 3: What if I went with something like:

class User < ActiveRecord::Base
      has_many :roles
      has_many :agendas, through: :roles
    end

    class Role < ActiveRecord::Base
      belongs_to :user
      belongs_to :agenda
    end

    class Agenda < ActiveRecord::Base
      has_many :roles
      has_many :users, through: :roles
    end

and then, in the migration file, go with something like:

class CreateRoles < ActiveRecord::Migration
  def change
    create_table :roles do |t|
      t.belongs_to :user, index: true 
      t.belongs_to :agenda, index: true
      t.string :privilege
      t.timestamps
    end
  end
end

Would I be able to call @user.agenda.privilege to get the privilege ("role" of creator, editor or viewer) of a given user for a given agenda?

Conversely, would I be able to call @agenda.user.privilege ?

Answer 1

Okay I will preface by saying I have not tested this but I think one of these 2 choices should work well for you.

Also if these join tables will never need functionality besides a relationship then has_and_belongs_to_many would be fine and more concise.

Basic Rails rule of thumb:

If you need to work with the relationship model as its own entity, use has_many :through. Use has_and_belongs_to_many when working with legacy schemas or when you never work directly with the relationship itself.

First using your example (http://repl.it/tNS):

class User < ActiveRecord::Base
  has_many :user_agendas
  has_many :agendas, through: :user_agendas
  has_many :user_agenda_roles, through: :user_agendas
  has_many :roles, through: :user_agenda_roles

  def agenda_roles(agenda)
    roles.where(user_agenda_roles:{agenda:agenda})
  end
end

class Agenda < ActiveRecord::Base
    has_many :user_agendas
    has_many :users, through: :user_agendas
    has_many :user_agenda_roles, through: :user_agendas
    has_many :roles, through: :user_agenda_roles

    def user_roles(user)
      roles.where(user_agenda_roles:{user: user})
    end
end

class Role < ActiveRecord::Base
  has_many :user_agenda_roles
end

class UserAgenda < ActiveRecord::Base
  belongs_to :user
  belongs_to :agenda
  has_many   :user_agenda_roles
  has_many   :roles, through: :user_agenda_roles 
end

class UserAgendaRoles < ActiveRecord::Base
  belongs_to :role
  belongs_to :user_agenda
end

This uses a join table to hold the relationship of User <=> Agenda and then a table to join UserAgenda => Role.

The Second Option is to use a join table to hold the relationship of User <=> Agenda and another join table to handle the relationship of User <=> Agenda <=> Role. This option will take a bit more set up from a CRUD standpoint for things like validating if the user is a user for that Agenda but allows a little flexibility.

class User < ActiveRecord::Base
  has_many :user_agendas
  has_many :agendas, through: :user_agendas
  has_many :user_agenda_roles
  has_many :roles, through: :user_agenda_roles

  def agenda_roles(agenda)
      roles.where(user_agenda_roles:{agenda: agenda})
  end

end

class Agenda < ActiveRecord::Base
    has_many :user_agendas
    has_many :users, through: :user_agendas
    has_many :user_agenda_roles
    has_many :roles, through: :user_agenda_roles

    def user_roles(user)
        roles.where(user_agenda_roles:{user: user})
    end
end

class Role < ActiveRecord::Base
  has_many :user_agenda_roles
end

class UserAgenda < ActiveRecord::Base
  belongs_to :user
  belongs_to :agenda
end

class UserAgendaRoles < ActiveRecord::Base
  belongs_to :role
  belongs_to :user
  belongs_to :agenda
end

I know this is a long answer but I wanted to show you more than 1 way to solve the problem in this case. Hope it helps