Results 1 to 4 of 4
  1. #1
    darylnet is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    2

    Question on creating relationship in table design

    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

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    no, 1 table for teams.
    tTeams have players from the tPlayer table.
    NYY, BOB SMITH, 23
    NYY, john jones. 31
    REDS, tim miller, 44

    tGames have teams,
    GameID, HomeID, AwayID, PlayDate
    9, REDS, NYY, 1/1/17

    tAtBat has players in that game.
    tAtBat table:
    gameID, BatID, AtBatID, PitcherID
    9, 1, 23, 44
    9, 2, 31,44

  3. #3
    darylnet is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    2
    Just to be clear. In my original construction, PlayerT contains TeamID along with list of all players in league. Is your recommendation that I reverse this so TeamT contains PlayerID? And if done this way, when I create GameT and choose Home team and Visiting team, will players associated with Home team and Visiting team be the only ones loaded under pitcher / batter fields? Thanks

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    When you add a game to the table, do you say which players played in that game? If not, then when you do the AtBat you will have all players from team 1 and all from team 2 in your selection. If your database contains which players were involved in each game then your selection will be from only that list.

    The AtBat table will contain game ID and bat-player ID and pitcher player ID.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. poor table relationship design
    By dsm2000 in forum Database Design
    Replies: 3
    Last Post: 10-26-2014, 11:50 PM
  2. Tricky design question to enforce a relationship
    By TABROCK in forum Database Design
    Replies: 2
    Last Post: 09-04-2014, 12:15 PM
  3. Relationship / Overall Design Question
    By nunzii in forum Database Design
    Replies: 2
    Last Post: 04-24-2013, 04:08 PM
  4. Problem with table and relationship design
    By fekrinejat in forum Database Design
    Replies: 4
    Last Post: 03-19-2013, 12:11 PM
  5. Table and Relationship Design
    By GrayWolf in forum Access
    Replies: 6
    Last Post: 01-31-2012, 02:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums