Mysql – How to structure MySQL database tables for users and how to handle adding/removing permissions

MySQLpermissionsPHProle

I am making a database structure for users, who can become part of a group, and within that group the admin of the group can assign as many permissions to a user as they would like.
This part seems easy. I have 4 tables:

 USERS           GROUPS             PERMISSIONS      PERMISSIONS_GROUPS
-------  ---------------------    --------------    -------------------
| UID |  | GID | UID(creator) |   | PID | NAME |    | UID | GID | PID |

I think this is the best way to handle this. I have a few questions about how to handle permissions for the creator of the group, and how to handle permissions if I were to delete a permission or add a permission.
For instance, when a user makes a group. Do I query all of the permissions and in a while loop insert a PERMISSIONS_GROUPS record for every row in the permissions table? If I add a new permission, do I have to query every UID from the GROUPS table and run a while loop to insert the new permission into PERMISSIONS_GROUPS? I just want to make sure I am not missing something simple here. For structure/scripting I am using MySQL and PHP.

Best Answer

A sketch for an alternative approach would be:

-- supertype for user / group, it is not clear from your post what 
-- an appropriate name would be, so I'll just use `x`. each x is of a 
-- certain type.

CREATE TABLE x_types
( x_type CHAR(1) NOT NULL PRIMARY KEY 
, -- maybe add a description 
);

INSERT INTO x_types (x_type) VALUES ('u'), ('g');
 

CREATE TABLE x
( xid ... NOT NULL PRIMARY KEY
, x_type CHAR(1) NOT NULL
, UNIQUE (x_type, xid)
-- common attributes for user / group
...
);   


CREATE TABLE users 
( uid ... NOT NULL PRIMARY KEY 
, x_type CHAR(1) DEFAULT 'u' NOT NULL
-- user specific attributes
, ...
, CHECK (x_type = 'u') -- does not work in previous version of MySQL
, FOREIGN KEY (x_type, uid) REFERENCES x (x_type, xid)  
);

CREATE TABLE groups 
( gid ... NOT NULL PRIMARY KEY 
, x_type CHAR(1) DEFAULT 'u' NOT NULL 
-- group specific attributes
, group_creator ... NOT NULL
, ...
, CHECK (x_type = 'u') -- does not work in previous version of MySQL
, FOREIGN KEY (x_type, gid) REFERENCES x (x_type, xid)
, FOREIGN KEY (group_creator) REFERENCES users (uid)  
);

-- a relationship between supertype and permission
CREATE TABLE x_permissions
( xid ... NOT NULL
, pid ... NOT NULL
, PRIMARY KEY (xid, pid)
, FOREIGN KEY (xid) REFERENCES x (xid)
, FOREIGN KEY (pid) REFERENCES permissions (pid)
);

-- group membership for users 
CREATE TABLE group_users
( gid ... NOT NULL REFERENCES groups (gid)
, uid ... NOT NULL REFERENCES users (uid)
, PRIMARY KEY (gid, uid) 
);

This may suit your needs, but it is hard to tell without knowing the exact business rules. Identifiers are of course just bogus and need to be adjusted.

Related Question