Database users and groups model

VeroLom picture VeroLom · Feb 18, 2011 · Viewed 7.7k times · Source

I have 2 entities: users and groups. Easiest model is user(id,name,etc...), group(id,name), user_group_rel(user_id,group_id). But I need to include groups into other groups. One user can be in many groups and groups can include users and subgroups with own users and subgroups! Need help with database model!

Users and groups

Answer

Adam Musch picture Adam Musch · Feb 18, 2011

While I agree with Ken Down's nomenclature, I don't necessarily agree that users and roles are the same entity.

Base Entities:

Users     (user_id, user name, real name, user_status, etc)
Role      (role_id, role name, role_password, etc)
Privilege (priv_id, base object, functionality, what have you)

Associative entities:

User has Role (0 - n) (user_id, role_id)
Role has Role (0 - n) (role_id, has_role_id)
User has Privilege (0 - n) (user_id, priv_id)
Role has Privilege (0 - n) (role_id, priv_id)