How to structure my database?

Question

My application is basically a way for groups to manage content.

There will be 3 different levels of users:

• Website Administrators: a few people who can do everything.
• Group administrators: privilege to upload files, edit calendars, and manage members, but only for their group.
• Members: can see content for the groups that they are members of.

Group admins should be able to remove members from the group. Anyone can be a member of a group, regardless of user level and can be members of multiple groups at one time.

The part that is confusing me is how to keep track of the multiple groups that a member can be a part of.

How should I structure my database so I can achieve this?

This is my idea so far:

//pseudo code:

users
    ( id , username , password , role ) //role within website

groups
    ( id , name )

group_members
    ( group_id , user_id , role ) //role within group

group_members VALUES
    ( 1 , 1 , 'group admin'  )
    ( 1 , 2 , 'group member' )
    ( 2 , 2 , 'group member' )

Is this how I should track membership of groups?

Answer 1

Looks fine to me, though I'd use an int id to indicate 'group member' or 'group admin' (instead of the text). In other words, have another table that denotes a user type and the id from that table would be assigned in group_members.

If there's only ever going to be one group admin, another way to do it would be to put a user id into the group table that indicates the group's admin. However, this would lock you into always having a single group admin.

Answer 2

Seems to be rather reasonable structure. However, maybe I would replace the textual role-field with reference to separate ROLE table, or with with integer that denotes the role.

group_members
  (group_id, user_id, role_id)

role
  ( role_id, name)

or

 group_members VALUES
    (1, 1, 1)  
 role 1 => group admin
 role 2 => group member

etc.

Answer 3

I'd change your role fields to either a boolean or a ENUM("Y","N") type thing and call them "is_admin" -- but besides that it looks good

Answer 4

USERS table

• user_id, pk
• username
• password

ROLES table

• role_id, pk
• role_name

GROUPS table

• group_id, pk
• group_name

USER_GROUP_ROLES_XREF table

• user_id, pk, fk
• role_id, pk, fk
• group_id, pk, fk

The USER_GROUP_ROLES_XREF table's primary key is a composite key - this means ensures that a user can only have one role per group because there could only be one record allowed with the combination of values. And because of the combination of all three values, a user can be involved with numerous groups.

Answer 5

I would do some changes:

//pseudo code:

users ( id , username , password , id_role ) //role within website

users_role (id, role)

groups ( id , name )

group_members ( group_id , user_id , id_group_role ) //role within group

group_role ( id, role)

group_members VALUES ( 1 , 1 , 1 ) ( 1 , 2 , 2 ) ( 2 , 2 , 2 )