I'm just learning so I apologize for what is probably a very basic question. I'm creating a dbase that tracks performance of baseball players on different teams.
1) I have a Team table that defines all the teams in the league
2) have a Player table that contains all the players in the league, and the team each plays on.
3) I have a GameID table that defines the date of the game and what 2 teams are playing (which is fed by a team table).
I want to be able to create an AtBat Table that allows me to specify the Pitcher from one team and the Batter from another team, and then records what happens during the at bat. How would I structure the relationship so that the only player options for the pitcher / batter in the AtBat Table are those that play on either of the 2 teams selected in the GameID table, as opposed to all of the players in the league?
Would I have to create a separate table for each team in the league that contains only the players that play on that team? Thanks