Better way to represent user roles in a database

The standard pattern for access control is called Role Based Security. As both the number of users and the number of different types of permissions you need grows, the management of your user-to-permissions links can become increasingly difficult.

For example, if you have five administrators and fifty users, how do you keep the permissions of each group in synch? When one of your users is promoted to an administrator, how many edits do you need to make? The answer is to create two intersections: users-to-roles and roles-to-permissions.

This solution is described (including entity relationship diagram) in my answer to this question.

Your first approach is feasible when the number of different roles/permissions is relatively small. For example if you only have two types of users: normal and admin, a separate table looks like an overkill. Single is_admin column is sufficient and simple.

However this approach does not scale once the number of roles exceeds a few. It has several drawbacks:

• user table becomes very "wide" having a lot of empty columns (wasting space)

• adding new role to the system requires altering user table. This is cumbersome and might be time-consuming for large user database

• listing user roles requires enumerating over all columns, as opposed to simple database query.