I have a table of individuals with the typical attributes (Individual ID (PK), name, address, city, state, zip)
These people can each belong to a number of groups. Each group having a number.
So Joe can belong to group 234 and 550. Sam belongs to 234 as well. Sue belongs to 623, etc
So therefore, there is a group table (group ID (PK), group number, name, other attributes for the group)
and an intermediate IndivGroup table that contains the group ID and Individual ID.
However, when forming groups, they can contain all members from another group. So, I can form a group 675 that contains all members form group 234 , 550, as well as a few other individuals. These individuals do not belong to 234 or 550.
I need to design something where I can not only list all the individuals, but "how" the group was created. In the brief example above, I would need to know that it includes everyone from 234 and 550 plus a few more individuals. I would also need to know all the individuals (Sam, Joe, etc) that are in the group.
When a new individual is added to 234, it will automatically be added to the list of members in 675.
Sounds like Individuals --> IndivGroup-->Group
but then also a Group <---Design Table (this would list the 234, 550 and individual IDs???) sounds like I'm combining apples and oranges. Individual IDs and Group IDs.
Whats the best way to solve this??