We help IT Professionals succeed at work.

Database Design and Query Question...

Medium Priority
522 Views
Last Modified: 2012-09-13
Evening Experts,

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.

People:
People_ID(PK)
People_Name

Teams:
Team_ID(PK)
Team_Name

Combinations:
Comb_ID(PK)
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)?
Comb_Score (Number)

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
where ???????

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??
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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".

Groups of 4 or 5 (both are listed)?
Also, when it comes to score, I assume you mean FINAL score since you say it can not change.  True?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>and I still dont know the "Right" method to solve it.

That is because there isn't a 'right' method to design.  It's whatever works.

Personally I like what you have with one exception:  I would make the combination table look like:
Comb_ID(PK)
Comb_T_ID (FK to Teams.Team_ID)
Comb_P_ID (Link to People.People_ID)
Comb_Score (Number)


I would control the number of members per team and number of teams with some other mechanism like a trigger or in application code.


>>Suppose I have a group of names and they are in multiple teams (No realistic way

I play Soccer and Basketball?

>>I enter the 4 names and want to see the team that has those 4 names with the highest score??

I'm really not understanding this one.  Are you asking that personA, B, C and D all exist on multiple teams like they all play basketball and soccer.  You then want to know which team that they all 4 play on has the highest score?

Author

Commented:
I'm sorry - lets go groups of 4 (Ignore 5).. Final score, yes - it will never change.


>> Are you asking that personA, B, C and D all exist on multiple teams like they all play basketball and soccer.  You then want to know which team that they all 4 play on has the highest score?

Correct!  Thats what I am asking. :)

I'm not understanding how your combination table would work (Which is par for the course since I'm asking the question. :) )

So if I only had one person per "Entry" like your combo table, I would have multiple entries per "team" instead of one like my example?

For instance My table would have data in the combo table like:

Comb_ID, Comb_T_Name, Comb_POne ,Comb_PTwo ,Comb_PThree,
Comb_PFour, Comb_Score

1,"FirstTeam",1,2,3,4,500


You would have that broken out into 4 different records, right?
Comb_ID(PK), Comb_T_ID, Comb_P_ID, Comb_Score
1,"FirstTeam",1,500
2,"FirstTeam",2,500
3,"FirstTeam",3,500
4,"FirstTeam",4,500
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>> would have multiple entries per "team" instead of one like my example?

You would have one entry for every member of every team.  This is an intersect table used to resolve a many-to-many relationship.

>>You would have that broken out into 4 different records, right?

Correct.

>>Correct!  Thats what I am asking. :)

If no other Expert posts an example and I get a little time later, I'll post a complete test case that, hopefully, will show it in action.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I was working on the example and came up with this question:
Does the team get a score of does the individual on a team get a score?
Do you need to capture team scores over time?

If individual:  It goes in the combinations table.
If Team: I would add it to the team table.
If over time: Create a team_event table.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
It's late for me and I'm not not able to come up with the magic SQL for your intersecting teams highest score.

Hopefully another Expert can provide it based on my setup.

I was able to create the test case that I believe is what you are after.  It is in Oracle syntax but should be ported easy enough.

drop table Team_members purge;
drop table Team_event_score purge;
drop table Teams purge;
drop table People purge;

create table People
(
People_ID number,
People_Name varchar2(15),
CONSTRAINT People_pk PRIMARY KEY (People_ID)
);


create table Teams(
Team_ID number,
Team_Name varchar2(15),
CONSTRAINT Team_pk PRIMARY KEY (Team_ID)
);

create table Team_event_score(
Event_ID number,
Event_Date date,
Team_ID number,
Team_Score Number,
CONSTRAINT Team_event_pk PRIMARY KEY (Event_id,Team_ID)
);

create table Team_members (
Team_memb_T_ID,	--Foreign Key
Team_memb_P_ID,	--Foreign Key
CONSTRAINT Team_members_pk PRIMARY KEY (Team_memb_T_ID,Team_memb_P_ID),
CONSTRAINT Team_memb_T_FK FOREIGN KEY(Team_memb_T_ID) references Teams(Team_ID),
CONSTRAINT Team_memb_P_FK FOREIGN KEY(Team_memb_P_ID) references People(People_ID)
);

insert into People values(1,'Moe');
insert into People values(2,'Larry');
insert into People values(3,'Curly');
insert into People values(4,'Shemp');

insert into People values(5,'Fred');
insert into People values(6,'Barney');

insert into People values(7,'Shatner');

insert into Teams values(1,'Stooges');
insert into Teams values(2,'Flintstones');
insert into Teams values(3,'Jews');
insert into Teams values(4,'Trek');

insert into Team_members values(1,1);
insert into Team_members values(1,2);
insert into Team_members values(1,3);
insert into Team_members values(1,4);
insert into Team_members values(1,7);
insert into Team_members values(2,1);
insert into Team_members values(2,2);
insert into Team_members values(3,1);
insert into Team_members values(3,2);
insert into Team_members values(3,3);
insert into Team_members values(3,4);
insert into Team_members values(4,7);

insert into Team_event_score values(1,to_date('01/01/2001','MM/DD/YYYY'),1,10);
insert into Team_event_score values(2,to_date('01/01/2002','MM/DD/YYYY'),1,20);
insert into Team_event_score values(3,to_date('01/01/2003','MM/DD/YYYY'),2,30);
commit;

Open in new window

Author

Commented:
slightwv, Thank you - the "Score" would never change after it was recorded. So I would put it in the "team" table.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
So a team only exists for a single project/event and will NEVER be together again?

I would probably go ahead and design for the most flexibility and create the team_score table.

I'll see if I can get some time later today to work on the SQL to come up with the high score where team members are the same.

Just to clarify the requirement:  Given my test case above, Stooges and Jews have the same 4 members and Jews have one additional member.  Will this 'match' since the same 4 exist in two teams or do the teams have to have the EXACT same members?

Author

Commented:
Slight, Again this is all just a learning thing for me, no real requirements:  

>> Will this 'match' since the same 4 exist in two teams or do the teams have to have the EXACT same members?

Yes, I would like to see it show as a "Match"

Thank you again!
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Don't thank me yet...  ;)

I just haven't had free time to play with this.  It is on my list of things to do!  I haven't forgotten, yet...
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Sorry but I have not had the time to dedicate to this.  The SQL necessary is pushing by abilities and will take more time that I have right now.

I can send out a call for help from additional Experts if you are still interested.  Just let me know.

Author

Commented:
Since I cannot ask another question till this one is closed. I am awarding points to slightwv for consistent replies.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.