Question Looking for Advice on Best Way to Setup a table with multiple options (1 Viewer)

[Zippyfrog]

I am trying to setup a database to track athletes and their off season conditioning. The athletes will be entering their workouts online, and then the coaches can access the data. The athletes register online, and one of the questions is to choose what sports they are participating (or think of participating in), and I was going to have a list of all the sports as checkboxes and they would choose all the sports they want.

Question is, what is the best approach to setup a table when an athlete can be a part of multiple sports? Here is my basic table setup:

tblAthletes
========
AthleteID
LastName
FirstName

If the athletes could only play one sport, it would be easy with a field called SportID as a foreign key to tblSports, which would have a SportID and Sport field. But if an athlete can choose multiple sports, and they could be in one, they could be in six, what is the best approach to take?

At first I was thinking of listing out all the sports on tblAthletes, each as their own Yes/No field as that would work well with the online form, but to me that seems like very bad database design with a lot of extra field space that will never be used. And if a sport were to ever be added, the actual table structure needs to be updated.

So I could use any advice. I feel like the best approach is still to have some sort of SportsID field that is a foreign key back to tblSports, but just can't figure out what to do when multiple sports can be chosen.

Thanks in advance!

 

[plog]

You need a junction table (http://en.wikipedia.org/wiki/Junction_table). You will have 3 tables: Athletes, Sports and AthletesSports. It sounds like you have the first two tables, you just need AthletesSports.

It will be at least 2 fields--an id from tblAthletes and an id from tblSports. That way multiple athletes can have multiple sports. This is what AthletesSports would look like:

ID_Athlete, ID_Sport
1,8
1,7
2,7
3,5
3,8


When you link that to the other 2 tables, you could translate that into this:

LastName, Sport
Smith, Baseball
Smith, Soccer
Jones, Soccer
White, Hockey
White, Baseball

 

[Zippyfrog]

Thanks! I have been reading up on the Junction Table, and that will do exactly what I want