How should I approach this relations in ruby?

Question

I've been going back and forward on this and I would like some advices.

I have "User" that can be part of many "Organizations", and for each one they can have many "Roles". (actually I have this scenario repeated with other kind of users and with something like roles, but for the sake of the example I summed it up).

My initial approach was doing a Table with user_id, organization_id and role_id, but that would mean many registers with the same user_id and organization_id just to change the role_id.

So I thought of doing an organization_users relation table and an organization_users_roles relation. The thing is, now I don't exactly know how to code the models.

class Organization < ActiveRecord::Base
   has_and_belongs_to_many :users, join_table: :organization_users
end

class User < ActiveRecord::Base
  has_and_belongs_to_many :organizations, join_table: :organization_users
end

class OrganizationUser < ActiveRecord::Base
   has_and_belongs_to_many :users
   has_and_belongs_to_many :organizations
   has_many :organization_user_roles
   has_many :roles, through: :organization_user_roles
end

class OrganizationUserRole < ActiveRecord::Base
  has_and_belongs_to_many :roles
  has_and_belongs_to_many :organization_users
end

class Role < ActiveRecord::Base
  has_and_belongs_to_many :organization_user_roles
end

If for example I want to get: ´OrganizationUser.first.roles´ I get an error saying: PG::UndefinedTable: ERROR: relation "organization_user_roles" does not exist

How should I fix my models?

Answer 1

To add to jaxx's answer (I upvoted), I originally thought you'd be best looking at has_many :through:

#app/models/user.rb
class User < ActiveRecord::Base
   has_many :positions
   has_many :organizations, through: :positions
end

#app/models/position.rb
class Position < ActiveRecord::Base
   #columns id | user_id | organization_id | role_id | etc | created_at | updated_at
   belongs_to :user
   belongs_to :organization

   belongs_to :role
   delegate :name, to: :role #-> @position.name
end

#app/models/organization.rb
class Organization < ActiveRecord::Base
   has_many :positions
   has_many :users, through: :positions
end

#app/models/role.rb
class Role < ActiveRecord::Base
   has_many :positions
end

This will allow you to call the following:

@organization = Organization.find x
@organization.positions
@organization.users

@user = User.find x
@user.organizations
@user.positions

---

This is much simpler than your approach, and therefore has much more ability to keep your system flexible & extensible.

If you want to scope your @organizations, you should be able to do so, and still call the users / positions you need.

One of the added benefits of the code above is that the Position model will give you an actual set of data which can be shared between organizations and users.

It resolves one of the main issues with jaxx's answer, which is that you have to set a role for every association you make. With my interpretation, your roles can be set on their own, and each position assigned the privileges each role provides.

Answer 2

If the user can have many Roles for a single organisation, and OrganizationUser represents this membership, than, yes, you need another table for organization_user_roles.

You need to explicitly create it in the database (normally with a migration)

To not get confused, try to find a nice name for OrganisationUser, like employment, membership, etc.

Answer 3

You should use a much simpler approach. According to your description, Roles is actually what connects Users to Organizations and vice-versa.

Using the has_many and has_many :through associations, this can be implemented like the following:

class User < ActiveRecord::Base
  has_many :roles, inverse_of: :users, dependent: :destroy
  has_many :organizations, inverse_of: :users, through: :roles
end

class Organization < ActiveRecord::Base
  has_many :roles, inverse_of: :organizations, dependent: :destroy
  has_many :users, inverse_of: :organizations, through: :roles
end

class Role < ActiveRecord::Base
  belongs_to :user, inverse_of: :roles
  belongs_to :organization, inverse_of: :roles
end

If you wish to preserve roles when you destroy users or organizations, change the dependent: keys to :nullify. This might be a good idea if you add other descriptive data in your Role and want the role to remain even though temporarily vacated by a user, for example.

The has_many :through association reference:

http://guides.rubyonrails.org/association_basics.html#the-has-many-through-association