3

I'm considering different designs for a generic role library gem.

The first is based off the Rolify gem but with a has_many through: relationship instead of HABTM. HABTM tables are "headless" in ActiveRecord and cannot be queried directly which is a huge con.

class User < ApplicationRecord
  has_many :user_roles
  has_many :roles, through: :user_roles
end 

class UserRole < ApplicationRecord
  belongs_to :user
  belongs_to :role
end 

class Role < ApplicationRecord
  has_many :user_roles
  has_many :users, through: :user_roles
  belongs_to :resource, polymorphic: true
end

Database diagram based off Rolify But looking at this I can't really see any distinct advantages to having a separate table vs joining users to roles directly. 

class User < ApplicationRecord
  has_many :roles
end 


class Role < ApplicationRecord
  belongs_to :user
end

Diagram - roles as a join

Having a separate Role table lets you perhaps create "global" roles and then attach users to the role.

But is it really worth the performance hit and added complication vs letting the roles be unique per user?

Improve this question
1
  • 2
    Why do you think the 1st solution is less efficient? In your 2nd solution you are duplicating name, resource_id and resource_type for every role_id x user_id. Certainly query time to search that list of duplicates would be greater than a 3 table join? Also consider that you are introducing the likelihood of data anomalies with your 2nd solution by introducing transitive dependencies to your roles table. Commented Sep 28, 2016 at 19:25

3 Answers 3

Reset to default
3

Having a separate Role table lets you perhaps create "global" roles and then attach users to the role.

But is it really worth the performance hit and added complication vs letting the roles be unique per user?

Absolutely. The advantage of having preset/saved roles that are available to be assigned to different users is:

  1. Each role can be tested, so there is confidence in assigning it to a users.
  2. Duplicated data is more difficult to change.
  3. Easier for the admin to assign the role if it is already built. The record gets also gets added without the need to duplicate the details of the role.
  4. It's important to structure your data for performance, but joining 3 tables shouldn't be that much of a problem to fine-tune.

If you're going to build a generic role library, you probably need the data management flexibility provided by a RDBMS. The many-to-many relationship you're creating is fairly common and shouldn't be that difficult to work with.

Improve this answer
1
  • 2
    Certainly these are all good points but the #1 reason why the first solution is desirable can be summed up in 2 words: Data Integrity. The second solution is guaranteed to produce data anomalies given enough time. Commented Sep 28, 2016 at 19:29
2

Hmm... there's hardly any performance hit for having the junction table (e.g., the user_roles table). Less than milliseconds: so far down in the noise you would never even catch a glimpse of any difference.

This junction table is standard normalization design and is quite recommended. If you have an Admin role, in your second suggested method you have no way to "control" that really... you might accidentally put in "Admine" instead of "Admin", then you're application breaks (or even worse, some screwup allows the wrong person access to something).

The only way to ensure that the "admin" role is in fact correct is by having a master list of unique roles. Your second suggestion doesn't have that.

There's really many reasons to do it the "correct" way, with a junction table, but to delve into all of them would be to give lessons on database normalization, which is a topic of some considerable breadth, depth and a lot of fundamentals.

Improve this answer
2

Generally when you are assessing the performance viability of a database design it is not enough to look at the number of tables being joined for a typical query. One must also evaluate the amount of data contained in the tables. If there were tens of millions of user records you might have a case to start being concerned about performance, however most systems do not have to worry about this number of users, at least initially.

Your second model is flawed in my opinion. You create a one-to-one relationship between Role and User. What this means is that if you have a Role called Administrator, then there can be one and only one User record that can have that role.

You have two options. One is by using the mapping/junction table that you defined in your first diagram. Typically we use these junction tables to create a many-to-many relationship between two tables.

  1. Can a User have more than one Role? (True|False)
  2. Can a Role have more than one assigned User? (True|False)

If you answer True to both of these questions then a junction table is necessary.

If you answered False to Question 1, then perhaps more appropriate is a foreign key constraint defined for Role ID column in the User table. This gives you One-to-One relationship with Roles table from User, and One-To-Many relationship from Roles to Users.

Improve this answer
2
  • Actually you are misunderstanding the second example - you would create many administrators by creating several roles with name = admin. There is no uniqueness constraint on roles.name. Commented Sep 28, 2016 at 19:23
  • 1
    @papirtiger My opinion doesn't change. How would you define this schema as an ERD? If you did then it would not make sense. This is a bad idea for no other reason that it is atypical for a Name field on a table to not have some general expectation of uniqueness. Each record of the Role table could not be uniquely described as an entity in a meaningful way (Eg. MarkAdministrator, SteveAdministrator, etc...). Commented Sep 28, 2016 at 19:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.