Better way to represent user roles in a database
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 it 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?
Answer
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.
