I'm having trouble figuring out how to model a few tables. I'm trying to find the tidiest solution possible, so perhaps someone has a definitive, tested-and-true way of achieving this:
Users, Groups and Permissions
Users and groups can each have multiple permissions set to them.
This is a simplified mock-up of what I have so far:
Code:
USER
id INT, AUTO_INCREMENT, NOT NULL
username VARCHAR(32), NOT NULL
password CHAR(32), NOT NULL
level INT, NOT NULL, DEFAULT 0
GROUP
id INT, AUTO_INCREMENT, NOT NULL
name VARCHAR(32), NOT NULL
USER_GROUP
id INT, AUTO_INCREMENT, NOT NULL
user_id INT, NOT NULL
group_id INT, NOT NULL
PERMISSION
id INT, AUTO_INCREMENT, NOT NULL
code VARCHAR(255), NOT NULL
Now, what would be the best way to associate
USERs and
GROUPs with
PERMISSIONs. I could create
USER_PERMISSION and
GROUP_PERMISSION tables, but is there a tidy way to merge these to a single table? The tidiness issue I have is with needing either separate
user_id and
group_id columns, and somehow requiring one to be NOT NULL (but not both) at a given time. The other option is a single
usergroup_id column, and another column to designate whether it's a user or group id (ENUM, is_group BOOLEAN, etc.)
TL;DR: Should I make both USER_PERMISSIONS and GROUP_PERMISSIONS tables, or is there an easier way?
dBforums
>
Trouble with Table Design Choices
10-11-10, 01:56
Linear Mode
