Many-to-Many Design, 2 kind of relationship on the same objects?

For example, one User has joined many Groups, one Group has many User members. This is a normal many-to-many relation. However, I want to identify users as 'member' and 'owner': One group will have many 'owner' and many 'member'; each user can be either owner or member of a group. As same time, 'owner' should also be a kind of 'member'. How should I create the table structure like this?

I ha开发者_如何学JAVAve the normal many-to-many relation like this, there also is a field in :group_user_relations table to identify whether the user is a member or a owner.

class User < ActiveRecord::Base
    # many-to-many to groups 
    has_many :group_user_relations
    has_many :groups, :through => :group_user_relations
end


class Group < ActiveRecord::Base
  # many-to-many to users 
  has_many :group_user_relations
  has_many :users, :through => :group_user_relations
  alias_attribute :members, :users
end

You need the following tables:

           USERS
           GROUPS
           ROLES
           USERGROUPROLE

The USERGROUPROLE table is this:

         userid  references USER
         groupid references GROUPS
         roleid  referencees ROLES

         Primary key (userid, groupid, roleid)

This would permit owner of a group to be also a member of the group. It would allow the group to have multiple owners and multiple members. A group has 0 or more members; a group has 0 or more owners.

You could have a PERMISSIONS and a ROLEPERMISSIONS table to identify the powers of particular roles. A ROLE has 0 or more permissions.

          ROLEPERSMISSIONS
          roleid references ROLES
          permissionid references PERMISSIONS

Sample permissions:

         can delete
         can edit
         can create new topic
         can attach file