We help IT Professionals succeed at work.
Get Started

Database Design and Query Question...

527 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
Most Valuable Expert 2012
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 1 Answer and 12 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE