Table Relationship Help

[JoshLewis]

I am creating a database for a high school athletic program. I currently have the following tables:

tblAthletes (AthleteID, name, date of birth, grade, etc)
tblSports (SportID, sport) - football, basketball, baseball, etc...
tblSeasons (SeasonID, season) - Fall, Winter, Spring.
tblLevel (LevelID, level) - C Team, Junior Varsity, Varsity.

One athlete can play multiple sports. Multiple sports are played per season, but each sport only has one season per year (Football, Fall or Basketball, Winter). Some sports have all 3 levels, some sports only have Varsity level.

I would like to ultimately be able to easily create rosters, injury reports (by Varsity Football, Junior Varsity Football, etc), and sort Athletes by season.

I'm looking for ideas on the best way to create table relationships with this information. As of right now, these are the relationships I've formed:

tblAthletes {AthleteID}
tblAthletes_Sport {AthleteID, SportID}


tblSports {SportID, Sport, SeasonID}

I'm not sure what to do with the Level data.

[June7]

Could put Level in tblSports which means Sport and Season will repeat for the various levels. The other place for this is another attribute in tblAthletes_Sport.

Suggest not saving ID but instead save the descriptive names for season, sport, level, because values are short. I am sure you won't run out of memory and this elminates lookups and table joins to retrieve names. Can still have tables as sources for combobox RowSources.