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.
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:
- 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).
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!