Hi I am attempting to store group, subgroup, and user information inside a database (SQLite).

group might have multiple subgroup, and user can fit in with multiple groups/subgroups as following.

Group 1 has subgroup a,b,c and user A,B.

Group 2 has subgroup d,e and user B,C,D.

subgroup d has user B,D and goes to group 2.

user B goes to Group 1,2 and subgroup a,c,d.

It will have the ability to looked by group, subgroup or user.

Thanks.

This is the way I'd get it done:

Group:
  id
  NULL overgroup_id

Membership:
  user_id
  group_id    

User:
  id

An organization which has overgroup_id = NULL is really a group, otherwise it is a subgroup.

To locate all customers_ids inside a group with id gid, with subgroups:

select user_id
from Membership
where group_id=gid
or group_id in
    (select group.id from Group where overgoup_id=gid)

The next table structure helps make the following presumptions:

  1. Subgroups are unique and distinct to every group, and therefore are distinct from actual Groups (one-to a lot of relationship, and Groups aren't able to be used as subgroups).
  2. Customers should be people with a minimum of one subgroup to be able to take part in an organization

    GroupTable GroupID (PK) GroupName

    SubGroupTable SubGroupID (PK) GroupID (FK on GroupTable.GroupID) SubGroupName

    UserTable UserID (PK) UserName

Now, produce a many-to-many relation table creating User participation within a number of sub-groups:

User_Sub_Groups
    UserID (FK on UserTable.UserID)
    SubGroupID (FK on SubGroupTable.SubGroupID)

If Groups can also be subgroups, then your example supplied by nulvinge is a option, though I'd get it done slightly in a different way:

GroupTable
    GroupID (PK)
    GroupName

UserTable
    UserID (PK)
    UserName

SubGroupTable
    ParentGroupID (Composite Key on GroupsTable.GroupID)
    SubGroupID (Composite Key on GroupsTable.GroupID)

UserGroupsTable
    UserID (Composite Key on UserTable.UserID)
    GroupID (Composite Key on GroupsTable.GroupID)

Came from here, you just make use of the JOIN between various tables to do your research. For instance, to come back all Customers who fit in with a particular Group:

SELECT     
    tblUser_Group.GroupID, tblUser.*
FROM         
    tblUser_Group 
INNER JOIN  
    tblUser ON tblUser_Group.UserID = tblUser.UserID
WHERE 
    tblUserGroup.GroupID = @GroupID

To come back all SubGroups which a particular User is really a member:

SELECT    
    tblGroup.GroupName AS SubGroupName
FROM         
    tblUser_Group AS UG 
INNER JOIN
    tblUser ON UG.UserID = tblUser.UserID INNER JOIN
    tblGroup_SubGroup AS GSG ON UG.GroupID = GSG.SubGroupID INNER JOIN
    tblGroup ON GSG.SubGroupID = tblGroup.GroupID
WHERE     
    tblUser.UserID = 1

And so forth. It may be difficult to think the right path with the various JOIN permutations in the beginning, but this can be a very flexible and scaleable arrangement.

Hope that can help!