Subform Design

[Zippyfrog]

Hi - I am trying to design a database to keep track of rosters for some athletic teams. Here is how my basic design is setup:

tblYear: Has two fields, YearID and AthleticYear, with Year ID being the primary ket
tblSport: Has two fields, SportID, and Sport
tblLevel: Has two fields, LevelID, and Level

Then, I have a table for Rosters.
RosterID - autonumber, primary key
YearID - foreign key linked to tblYear's YearID
SportID - foreign key linked to tblSport's SportID
LevelID - foreign key linked to tblLevel's LevelID
LastName
FirstName
Number
Postion, etc...

I have a two part question - first, would that be the most normalized way to make the database? Second, I want to have a form to quickly create roster. On my main form I have three combo boxes - one for the year, one for the team, and one for the level, then I created a subform that would quickly be used for roster creation. The issue is that I can't have three values automatically populate in the table as I can only have one parent/child field relationship.

How would I go about making this work? I have attached a screenshot of what I am trying to do. Basically, I want to be able to choose the three combo box values, then add the information to tblRoster without needing to choose the sport, level, and year each time.

 

[ajetrumpet]

from what I know about normalization, I think what you've done here is OVERnormalized the whole thing. In this man's opinion, this might prove to be optimized at the table leveL:

TABLE 1: TEAMS
field - ID (unique - AN possible here)
field - name of the team if ya got one
field - ATHLETIC YEAR
field - SPORT
field - LEVEL

TABLE 2: ROSTERS
(all the fields that have to do with the players of your rosters here)


There are only 2 entities in this scenario. a team, and the associated roster. that's about all. perfect really for a 1-many relationship. the subform setup should be cake after you get this part, if you use this post to your advantage...

 

[highandwild]

It looks like you are going to have the athletes name duplicated in the rosters table.

Have an athletes table with an ID, their name, height and weight
and use the ID in the rosters table.

 

[Zippyfrog]

Thanks for the feedback. I didn't know it was possible to over-normalize. That setup makes perfect sense.

And with my athletes, that dropdown box is tied to a table of athletes, each with their own ID number, so I have taken care of that potential duplicate issue.

Thanks again!