Relational View

The requirement as I see it: User can be part of many groups and each group can be part of other group.

• Option 1:
  • The users table will have an additional column called groupid(fk)
  • Group table: groupId(pk), group name, parentgroupid (group can have a parent, grand parent... hierarchy).
• Option 2:
  • Have an additional column in the user table called parentuserid. In that case users can have a parent-child relationship and group will just be another user.

• Disadvantages
  • option 1: we cannot have one user in many groups, unless we create a group for each user and aggregate that group.
  • option 2: We need to enter the user information like email id, etc for all the groups.

Object based view

That was more of a Relational view of the database, but as Randy pointed out, an object based view of the model will be more appropiate. Here is Randy's suggestion on an object based model

• Option 1: WebDAV User Model
  

• Option 2: Following the java security model, It should be like
  

The java security package defines some neat interfaces for ACL:

• http://java.sun.com/j2se/1.4.2/docs/api/java/security/Principal.html
• http://java.sun.com/j2se/1.4.2/docs/api/java/security/acl/Group.html

• Option 3: The new ACL interfaces in the Acegi sandbox provides a nice support for any kind of security. At present acegi supports role based security, but this can be extended to provide Group based security as illustrated below - the GroupSid^? will be a new class (the red class) for implementing group based security:
  

Relational view based on the object mapping