Multivalued fields in Sports Card Database (1 Viewer)

cmo187265

New member
Local time
Today, 15:08
Joined
Aug 31, 2019
Messages
3
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!
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 20:08
Joined
Feb 19, 2013
Messages
12,237
What is the best practice for an issue like this?
use a many to many table - which is what a multivalue field effectively does, but it is hidden away and much more limited in scope.

I don't quite understand your table structure for players, the normal way to do this would be to have a primary key to identify the player and a table of aliases

i.e.

tblPlayers
PK...Name..............DOB
1.....Jack Black........1/1/1980
2.... Horatio Nelson..12/4/1985

tblAliases
PK...PlayerFK....Alias
1.....1..............Blacky
2.....1..............Pirate
3.....2..............Admiral
4.....2..............The Admiral
 

cmo187265

New member
Local time
Today, 15:08
Joined
Aug 31, 2019
Messages
3
Thank you CJ London.

You are talking about what is sometimes called a Many to Many Junction/Join Table?

My TblPlayers is structured how you show it as being structured. I just failed to mention I have PK (autonumber).

I was only saying that the Players Table won't be normalized into First Name / Last Name Fields because in many cases it's not possible. With some older players in the early 1900s the only names on file are nicknames (Aliases) so I need to use one Name field.
 
Last edited:

isladogs

CID
Local time
Today, 20:08
Joined
Jan 14, 2017
Messages
13,605
In that case have 3 fields: FirstName, LastName and Alias in your table as well as the PK field.
You can then use a query or sql string to concatenate the three fields as required for forms and reports. You can use the Trim or Nz functions to manage empty fields
e.g. Trim(Alias & " " & FirstName & " " & LastName)
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 20:08
Joined
Jul 9, 2003
Messages
11,791
I've never used Multivalued Fields myself, however I did find these excellent videos:- Multi Value Fields Links which I kept a link to, just in case I was tempted!

Regarding "Many to Many", the name is misleading - “Many to Many”! It can only ever be a one-to-many twice, two, one from each side. The only thing that is many-to-many is the actual table the data is stored in. You can never utilise the table directly to produce a many-to-many representation. Caveat as far as I'm aware! You can only have "One to Many" based on it as I explained in a blog here:-

Many to Many Relationship - 3 Video's

The videos Video 1 and Video 2 show the many to many relationship from both sides. One side, using students matched against hobbies, and the other side, hobbies matched against students. There is an extra video showing you one or two problems you might have, and the solution(s).
 

isladogs

CID
Local time
Today, 20:08
Joined
Jan 14, 2017
Messages
13,605

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom