Database users and groups model

Question

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!

Answer 1

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)