Multivalued fields in Sports Card Database

[cmo187265]

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!

 

[CJ_London]

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

 

[isladogs]

See this article about Multivalued fields and why you shouldn't use them

 

[cmo187265]

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.

 

[isladogs]

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)

 

[NauticalGent]

See this article about Multivalued fields and why you shouldn't use them

Col,

That article is simply brilliant! Everything is there including a visual explanation which proves your assertion.

Well done.

 

[isladogs]

Col,

That article is simply brilliant! Everything is there including a visual explanation which proves your assertion.

Well done.

Many thanks. There are many other extended articles about specific Access issues on the website http://www.mendipdatasystems.co.uk/access-articles/4594431956.
Each has a similar structure to the one you read.
You may find something else that interests you