I am trying to configure a mysql database table that I can use as a per user/group permissions table. Almost like what Facebook has, for a local company intranet site.
What is the best possible way to design this table, that would allow me to find user permissions with the fewest amount or queries?
Best Answer
There are several ways to manage permissions. Wanting to validate these in the database, one of the possible options is to establish a list of permissions, and associate these permissions to possible actions by the user (the creation of groups is a consequence of this report).
Assuming then a very simple case, where the user decides to perform an action on the interface, for example, "add a comment to a post," a possible workflow is as follows
System
Interface
Pressing a button in the interface invokes the "Add Comment" (which acts on the interface to create a text field and a button, if that user has permission to do so. Otherwise the error message will be shown.)
API
All of the methods, not public, in the backend, provide for the control of permissions before running the rest of the code. So the query is executed in the database to see if the user has permission for action "Comment".
Database
The database performs a select in the table where are recorded users and actions. Responding with a NULL (there is no permission) or ID (allowed).
At this point we can assume that:
Groups
The groups should (could) be managed as presets of permission. There are so many ways to manage the permissions of the group. Probably one of the least expensive in terms of development, is to create a table for managing the relationship between Groups and Users.
Conclusions
The structures of our database will therefore be as follows:
Users
Actions
Permissions
Groups