Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

Thread Tools Rate Thread Display Modes
Prev Previous Post   Next Post Next
Old 08-31-2019, 02:32 PM   #1
Newly Registered User
Join Date: Aug 2019
Posts: 3
Thanks: 5
Thanked 0 Times in 0 Posts
cmo187265 is on a distinguished road
Multivalued fields in Sports Card Database

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.

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.

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 by cmo187265; 08-31-2019 at 02:33 PM. Reason: Baseball DB changed to Sports Card DB (content)
cmo187265 is offline   Reply With Quote

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating a database to track sports events and children swellybro General 2 09-30-2017 06:55 AM
Using Filter Property with multivalued fields cmpd Queries 1 12-05-2013 06:09 AM
Multivalued Fields zyousafi Tables 2 12-14-2011 04:43 PM
Question Sports Card Database (Buying, Selling, etc.) hunterfan48 General 6 08-17-2008 10:06 PM
Sports Database Create Your Own League Lightwave Sample Databases 0 04-12-2006 05:44 AM

All times are GMT -8. The time now is 03:09 PM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World