how to store list of numbers to database

Hi I'm trying to store group, subgroup, and user information in a database (SQLite). A group can have multiple subgroup, and user can belong to multiple groups/subgroups as following.

• Group 1 has subgroup a,b,c and u开发者_StackOverflow社区ser A,B.
• Group 2 has subgroup d,e and user B,C,D.
• subgroup d has user B,D and belongs to group 2.
• user B belongs to Group 1,2 and subgroup a,c,d.

It should be able to searched by group, subgroup or user.

The following table structure makes the following assumptions:

1. Subgroups are unique and distinct to each group, and are distinct from actual Groups (one-to many relationship, and Groups are not useable as subgroups).

2. Users must be members of at least one subgroup in order to participate in a group

   GroupTable GroupID (PK) GroupName

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

   UserTable UserID (PK) UserName

Now, create a many-to-many relation table establishing User participation within one or more sub-groups:

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

If Groups are also able to be subgroups, then the example provided by nulvinge is one option, though I would do it slightly differently:

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)

From here, you simply use the JOIN between various tables to perform your search. For example, to return all Users who belong to a certain Group:

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

To return all SubGroups of which a specific User is 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 on. It can be challenging to think your way through the various JOIN permutations at first, but this is a very flexible and scaleable arrangement.

Hope that helps!

This is how I would do it:

Group:
  id
  NULL overgroup_id

Membership:
  user_id
  group_id    

User:
  id

A group that has overgroup_id = NULL is a group, otherwise it's a subgroup.

To find all users_ids in 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)