Eliminate the Join Table in HasMany Relationships in Rails

Question

I am thinking about ways to create a Role Based Access Control system in Rails. I have seen these great projects too (among others):

• RoleRequirement
• ActsAsPermissible
• RailsAuthorization
• BoxRoom (rails file management)

My question is, is it really necessary to have a join table for everything? If I one of the tables in the relationship only has a few values (say less than 100), can't I just merge the join table with that small table?) This is what I mean... Here's what I need:

Models

• User
• Group
• Role
• Permission
• UserRoles/RolesUsers
• GroupRoles
• Memberships (GroupUsers)
• RolePermissions

Something like that...

The way RoleRequirement works is by creating a roles table and a roles_users join table. This means that if I have 20 total possible Roles in an application, I have

• Role table with 20 rows
• RolesUsers table with n rows.

Which means every time I want to find a user by role, I have to do a join. I'm wondering though, since there will only be a few Roles in an application, why not just replace this migration:

create_table "roles", :force => true do |t|
  t.string "name"
end

create_table "roles_users", :id => false, :force => true do |t|
  t.integer "role_id"
  t.integer "user_id"
end

with this one...

create_table "roles", :force => true do |t|
  t.string "name"
  t.integer "user_id" # or some polymorphic form
end

That would cause duplication (tons of roles named "admin" for example), but since space is cheap, and we could create a method like Role.unique to find all unique Roles (to get rid of that 20-row table), why do people create the join table?

Same thing with Permissions: I only will likely have 4 permissions to start: create read update delete. So I don't need a permissions table and a roles_permissions table, I could just duplicate the CRUD permissions and have the role_id in the permissions table. Same with Group, I don't need Group Roles if I had polymorphic columns in my roles table.

What is the recommended way to do this?

Here is a snippet of the proposed migration.

Answer 1

I would not suggest you do this. What you describe is called denormalization.

Denormalization introduces problems for many applications and should only be done if you have a clear need for it. I typically only denormalize tables for reporting purposes.

Your question doesn't show that you have any need to denormalize. Rather, it shows a misguided aversion to having "extra" tables, and avoiding a simple join. Having duplicated data costs more than just space, it also costs performance (unique isn't a freebie). Modern RDBMS are quite adept at handling joins.

I'd suggest googling and searching SO for information on denormalization. There is no golden rule, but your case doesn't seem to have any good reason to.

If you're looking for performance gains, throw ActiveRecord in the trash. There are many alternatives, and you can write your own.