I am new to Access and Databases and fairly new to forums over the past year so hope I post correctly.
I have been normalizing a Sports Card database which will eventually hold thousands and thousands of cards and it is more complex than one might think. My question revolves around multivalued fields.
Summary:
In a table (say TblCards) there will be a record for each card that has fields pulling from other tables (TblSports, TblTeam, TblBrand, TblColor, etc.). One very important field "Players" will pull from a TblPlayers which will be a separate table with thousands of Player Names.
Summary Normalization Note:
- Player Names will be full names in one field to account for naming conventions of different countries/cultures and names where historic records of a player's name may result in unorthodox nicknames that do not follow First Name/Last Name convention (i.e. Jose De La Cruz III , Homerun Baker, Shoeless Joe Jackson)
- In TblPlayers a second field (either DOB or Debut date) will exist to differentiate between players with the exact same same.
Summary of Problem:
In Tbl Cards one Card/Record may have many Players from TblPlayers. For example a Team Card may picture an entire roster of a specific team. So in football for example this could be 22 Players. Many Team cards will exist. I would need the ability for a Card to show up in a search for any player shown on the card.
Question...finally!:
I have heard horrors of multivalued fields but struggle to understand an alternative. I am currently reading about many to many junction tables as a solution but wonder if that is viable with so many options/players to chose from. What is the best practice for an issue like this?
I appreciate any and all suggestions and feedback!
I have been normalizing a Sports Card database which will eventually hold thousands and thousands of cards and it is more complex than one might think. My question revolves around multivalued fields.
Summary:
In a table (say TblCards) there will be a record for each card that has fields pulling from other tables (TblSports, TblTeam, TblBrand, TblColor, etc.). One very important field "Players" will pull from a TblPlayers which will be a separate table with thousands of Player Names.
Summary Normalization Note:
- Player Names will be full names in one field to account for naming conventions of different countries/cultures and names where historic records of a player's name may result in unorthodox nicknames that do not follow First Name/Last Name convention (i.e. Jose De La Cruz III , Homerun Baker, Shoeless Joe Jackson)
- In TblPlayers a second field (either DOB or Debut date) will exist to differentiate between players with the exact same same.
Summary of Problem:
In Tbl Cards one Card/Record may have many Players from TblPlayers. For example a Team Card may picture an entire roster of a specific team. So in football for example this could be 22 Players. Many Team cards will exist. I would need the ability for a Card to show up in a search for any player shown on the card.
Question...finally!:
I have heard horrors of multivalued fields but struggle to understand an alternative. I am currently reading about many to many junction tables as a solution but wonder if that is viable with so many options/players to chose from. What is the best practice for an issue like this?
I appreciate any and all suggestions and feedback!
Last edited: