User and user group model

Question

I have 2 objects: users and groups. The simplest model is the user (id, name, etc.), Group (id, name), user_group_rel (user_id, group_id). But I need to include groups in other groups. One user can be in many groups, and groups can include users and subgroups with their own users and subgroups! Need help with the database!

Answer 1

Although I agree with the Ken Down nomenclature, I do not always agree that users and roles are one and the same entity.

Base objects:

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 objects:

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)

Answer 2

, "". , , - . "login" true, , , "login" , .

, . ( ), : . "login" .

EDIT. , @XIVSolutions, , . , . , , . , , , .

#DIT: : @XIVSolutions, - () .

Answer 3

re: Ken Downs:

( , , ...):

1:

**tblRole**
RoleID PK
RoleName

**tblRoleIndex**
ParentRoleID FK on tblRole  
ChildRoleID FK ON tblRole

NOTE: ParentRoleID and ChildRoleID form a composite Primary key in the above table.

**tblLogIns**
LogInID PK
RoleID
PassWord

, 2:

**tblRole**
RoleID
ParentRoleID Recursive FK on tblRole.RoleID
RoleName

NOTE: A top-level role in the table above has a default ParentRoleID of -1 or 0

**tblLogIns**
LogInID PK
RoleID
PassWord

Answer 4

The key to modeling this is how to treat both users and groups as groups. The Ken Downs approach will solve this problem. Another approach is to consider users and groups as a derived type of a common base type:

create table groupmemberbase
(
  memberid int,
--optional flag to idicate the type of the derived entity
--this is similar to the login flag used by Ken Downs 
  membertype int,
  primary key (memberid)
)

create table users
{
  memberid int,
--more user attributes here
  primary key (memberid),
  foreign key (memberid) references groupmemberbase (memberid)
)

create table groups
(
  memberid int,
--more group attributes here
  primary key (memberid),
  foreign key (memberid) references groupmemberbase (memberid)
)

create table groupmembers
(
  groupid int,
  memberid int,
  primary key (groupid, memberid),
  foreign key (groupid) references groups (memberid)
  foreign key (memberid) references groupmemberbase (memberid)
)

One of the advantages of this approach is that it prevents a user or group from accidentally becoming a member of the user - this restriction applies at the schema level.