Take the 2-minute tour ×

Is representing user permissions better in the user table or better in its own permissions table?

Permissions in User table
Putting permissions in the user table means making a column for each permission in the user table. An advantage is queries should run faster because no joins are necessary when relating users to user permissions. A disadvantage is that having many permissions columns clutters the user table.

Permissions in Permission table joined to User table with many-to-many relationship
Doing it this way cleanly separates out the permissions from the user table, but requires a join across two tables to access user permissions. Database access might be slower, but database design seems cleaner.

Perhaps keeping permissions in a separate table is better when there are many permissions. What are other considerations in making this decision, and which design is better in various situations?

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
Enumerating permissions columns to show all rolls and running ALTAR command on the db - two things I didn't think of. The separate table quickly becomes more desirable. –  steampowered Oct 27 '12 at 18:46
add comment

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.

enter image description here

share|improve this answer
This is a robust answer for a more complex application. As @Nurkeiwicz notes, a simple application may not even need a rolls table or permissions table. –  steampowered Oct 29 '12 at 17:53
nice diagram, what tool did you use? –  dangerousdave Dec 13 '13 at 10:38
@dangerousdave - Sorry for delayed response, I've been out of town. I use Visio with custom smart shapes that I built to use the James Martin ERD visual convention and a custom line pattern that gives it a hand-drawn look. –  Joel Brown Dec 16 '13 at 17:16
@Joel - it's very good! –  dangerousdave Dec 17 '13 at 18:53
add comment

Your Answer


