?
Solved

Database Design and Query Question...

Posted on 2012-08-14
12
Medium Priority
?
509 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??
0
Comment
Question by:hej613
  • 7
  • 4
12 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38294255
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?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38294275
>>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?
0
 
LVL 1

Author Comment

by:hej613
ID: 38294348
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
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38294359
>> 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.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38294458
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.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38294504
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

0
 
LVL 1

Author Comment

by:hej613
ID: 38295746
slightwv, Thank you - the "Score" would never change after it was recorded. So I would put it in the "team" table.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38295786
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?
0
 
LVL 1

Author Comment

by:hej613
ID: 38301058
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!
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38301086
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...
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1500 total points
ID: 38345947
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.
0
 
LVL 1

Author Closing Comment

by:hej613
ID: 38396444
Since I cannot ask another question till this one is closed. I am awarding points to slightwv for consistent replies.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question