Question :
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
.
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.