I am trying to understand the best way to work with permissions and as far as I'm aware there are two major options.
The first option is to use bitwise operations, and for this I would use the following database structure:
users
user_id | user_permission
---------------------
1 | 15
2 | 1
permissions
permission_id | permission_name
-----------------------
1 | Read
2 | Write
4 | Execute
8 | Delete
And then to check that the user has permission I would use the operation:
$user_permission & $permission_id
The main benefits I see to this are:
The main drawbacks I see to this are:
BIGINT
)The second option is to use a many-to-many child table, and for this I would use the following database structure:
users
user_id
-------
1
2
permissions
permission_id | permission_name
-----------------------
1 | Read
2 | Write
3 | Execute
4 | Delete
user_permissions
user_id | permission_id
-----------------------
1 | 1
1 | 2
1 | 3
1 | 4
2 | 1
And then to check that the user has permission I would use the operation (where $user_permission
is an array of permission_id
s):
in_array($permission_id, $user_permission);
The main benefits I see to this are:
The main drawbacks I see to this are:
Question
Which of these would be the better option? I see benefits and drawbacks to each and am unsure which would be more suitable. Although I am aware that context probably plays a role; so in which situations would bitwise operations be better and in which would a many-to-many child table be better? Or is there a third option of which I'm unaware?
I'm currently more inclined to use a many-to-many table for the benefits of foreign key constraints and a greater number of permission possibilities, but I wonder if I'm missing something else; bitwise operation permissions seem to be quite prevalent so I'd assume there is a good reason for using them.
I would not go with the bitwise operations solution. Unless you are really really cramped for space, breaking this out into its own table and mapping table won't cost that much disk. It would be easier for people who aren't you to understand, and you can more easily enforce FK relationships this way. Also, as you mentioned, the number of permissions can grow virtually without limit. Depending on how you index the table, queries like "show me all users with Read permission" seems like it would be quicker to execute and easier to understand (that's subjective, I realize).