Database Schema For Users belonging to Groups with Roles

The best way would be a pivot table with an extra field

users
- id 
- email
- password  

groups
- id
- name

group_user
- group_id
- user_id
- role (owner, creator and so on)

If you want multiple roles for a group_user you will need an extra pivot table

@bobbybouwmann So basically role would be simple string rather than having extra Table just for roles.

You can have an extra table for roles, however that means that you need to connect the user to the that role and the user_group table as well. That means an extra table.

@bobbybouwmann If we go adding extra attribute in pivot table how would I query just to retrieve organization where user is admin.

Just add the keyword in there. So you would have a record like so

group_user

group_id => 1
user_id => 1
role => "admin"

group_id => 1
user_id => 2
role => "member"