title: Design of the database with multiple user roles
body: <p>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.</p>
  
  <p>I have 4 types of users:</p>
  
  <pre><code>Admin
  Teacher
  Student
  Librarian
  </code></pre>
  
  <p><code>Admin</code> is simple and he would need only two columns, <code>username</code> and <code>password</code>. User table at the beginning contains only these values:</p>
  
  <pre><code>username, password, admin, teacher, student, librarian
  </code></pre>
  
  <p>Columns <code>admin, teacher, student, librarian</code> are booleans which define does user have certain role. Admin would have <code>admin</code> to <code>true</code> and all other columns to false. </p>
  
  <p>The problem is appearing when we introduce other columns which are specific to only one role. The we have these columns for user table:</p>
  
  <pre><code>id
  username
  password
  admin
  teacher
  student
  librarian
  teacher_column1
  teacher_column2
  teacher_column3
  student_column1
  student_column2
  student_column3
  etc...
  </code></pre>
  
  <p>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. </p>
  
  <p>On a couple of places when people asked about this, they were recommended to do STI so they would end with something like this:</p>
  
  <pre><code>class User &lt; ActiveRecord::Base
  
  end
  
  class Admin &lt; User
  
  end
  
  class Teacher &lt; User
  
  end
  
  class Student &lt; User
  
  end
  </code></pre>
  
  <p>This looks much better but would still introduce a lot of null values.</p>
  
  <p>Other idea is to introduce 4 new tables for different user type details. Something like this:</p>
  
  <p>User table:</p>
  
  <pre><code>id
  username
  password
  admin
  teacher
  student
  librarian
  </code></pre>
  
  <p>Teacher table:</p>
  
  <pre><code>user_id
  teacher_column1
  teacher_column2
  teacher_column3
  </code></pre>
  
  <p>Student table:</p>
  
  <pre><code>user_id
  username
  password
  student_column1
  student_column2
  student_column3
  </code></pre>
  
  <p>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.</p>
  
  <p>What do you think about this last approach? Cons and Pros?</p>
  
  <p>Would this be a correct way in creating a system with multiple user roles? Thank you.</p>