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?
MySQL PHP User/Group Permissions
mysql;permissions
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 followsSystemInterfacePressing 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.)APIAll 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.DatabaseThe 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:Any private action (method exposed in the API), after the validation of input parameters, performs the validity check.The function in the backend, to authorize the execution of code takes 2 parameters (minimum): UserID, ActionID;The function in the database (or the query to be executed in the database) involves reading in a table that has 3 fields (minimum): ID, user ID, ActionID;GroupsThe 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.ConclusionsThe structures of our database will therefore be as follows:UsersUserIDUsername...ActionsActionIDActionName...PermissionsIDUserIDActionIDGroupsIDGroupIDUserID