I have ran across a problem like this multiple times, and I still dont know the "Right" method to solve it.
I dont have any real world projects going on right now where this is a problem, so I'll make up an example so I hope its understandable.
I have a group of 100 people and I want to break them down into different groups of 4 - Some of them CAN be in multiple groups, some in just one.
Each "Team" or group of 5 are awarded a different score and I need to keep up with that, the score should never change, so I can record them in the same table with the "Teams".
In my understanding I would have 3 tables for this.
Comb_T_Name (FK to Teams.Team_ID)
Comb_POne (Link to People.People_ID)
Comb_PTwo (Link to People.People_ID)?
Comb_PThree (Link to People.People_ID)?
Comb_PFour (Link to People.People_ID)?
Is this the correct way to Design something like this (I know, its very simplified) How would I query against it Provided it is correct? For Instance if I wanted to know every team that "Bob" was in I could do something like:
Select Team_Name || ' - ' || People_Name
from People, Teams, Combinations
How would I write a query that would take into account if "Bob" was entered into POne, PTwo, PThree, or PFour?
I know it will need a "Interesting" Join...
For a Follow up - Suppose I have a group of names and they are in multiple teams (No realistic way *WHY* I would have it, but) I enter the 4 names and want to see the team that has those 4 names with the highest score??