Design of the database with multiple user roles

Question

Many of us have probably encountered this situation. I want to build an app which supports multiple user roles and don't want to use gems for that process.

I have 4 types of users:

Admin
Teacher
Student
Librarian

Admin is simple and he would need only two columns, username and password. User table at the beginning contains only these values:

username, password, admin, teacher, student, librarian

Columns admin, teacher, student, librarian are booleans which define does user have certain role. Admin would have admin to true and all other columns to false.

The problem is appearing when we introduce other columns which are specific to only one role. The we have these columns for user table:

id
username
password
admin
teacher
student
librarian
teacher_column1
teacher_column2
teacher_column3
student_column1
student_column2
student_column3
etc...

When user is created we need to fill a lot of columns with null values. If we create a teacher, all columns which are needed only for librarian and student are filled with nulls.

On a couple of places when people asked about this, they were recommended to do STI so they would end with something like this:

class User < ActiveRecord::Base

end

class Admin < User

end

class Teacher < User

end

class Student < User

end

This looks much better but would still introduce a lot of null values.

Other idea is to introduce 4 new tables for different user type details. Something like this:

User table:

id
username
password
admin
teacher
student
librarian

Teacher table:

user_id
teacher_column1
teacher_column2
teacher_column3

Student table:

user_id
username
password
student_column1
student_column2
student_column3

The same for Librarian and Admin. In the process of user creation after we check role, new row in specific table for that user would be created.

What do you think about this last approach? Cons and Pros?

Would this be a correct way in creating a system with multiple user roles? Thank you.

I am not sure if you have seen github.com/RailsApps/rails-devise-roles or github.com/RolifyCommunity/rolify or github.com/ryanb/cancan is the one I use the most — MZaragoza
Why not use gems? You can easily solve your problem in a very flexible way using "Multi Table Inheritance" (Implemented in Heritage: github.com/dipth/Heritage). If you need more details I can show you how it can be done. — Panickos Neophytou

SHS SHS · Accepted Answer · 2014-12-23T18:30:08

Don't use different columns for each permission.

Create a single table for Users with these columns:

username
password
user_type

user_type will be a value in %w{admin teacher student librarian} Then use single-table-inheritence like you mentioned in your post.

Create a table for Role:

name

Then create a table to tie roles to users:

role_id
user_id

Create a table for Permission:

name

Then create a table to tie permissions to roles:

permission_id
role_id

In the end you will have:

3 models with tables: User, Role, and Permission
4 models without tables, inherting from User: Admin, Teacher, Student, and Librarian
2 tables without models: users_roles and roles_permissions

Also, if there are many columns that are unique to every user-type, then I would recommend using different model+tables for each and making the user in Role polymorphic.

That way you can easily create new roles/permissions, modify the permissions of a particular role, and easily add/remove roles for a particular user.

Design of the database with multiple user roles

1 Answers