0

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.

3
  • All creators will have same permissions? Or creator permission changes with each group? – DEarTh Feb 7 at 8:06
  • If all creators have same permissions then store that as user meta like key 'creator_permissions', value '1,5,7,8,9,10' , you can store that as stringify or comma separated values. – DEarTh Feb 7 at 8:09
  • You could also create another table to store creator permissions. This will also allow you to set different permissions for each group creator. – DEarTh Feb 7 at 8:10
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.