Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Theory and practice of database design (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=74)
-   -   Multivalued fields in Sports Card Database (https://www.access-programmers.co.uk/forums/showthread.php?t=306639)

cmo187265 08-31-2019 02:32 PM

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.

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 08-31-2019 02:47 PM

Re: Multivalued fields in Sports Card Database
 
Quote:

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 08-31-2019 02:51 PM

Re: Multivalued fields in Sports Card Database
 
See this article about Multivalued fields and why you shouldn't use them

cmo187265 08-31-2019 03:14 PM

Re: Multivalued fields in Sports Card Database
 
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 08-31-2019 08:56 PM

Re: Multivalued fields in Sports Card Database
 
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 09-01-2019 01:13 AM

Re: Multivalued fields in Sports Card Database
 
Quote:

Originally Posted by isladogs (Post 1638223)

Col,

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

Well done.

Uncle Gizmo 09-01-2019 02:46 AM

Re: Multivalued fields in Sports Card Database
 
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 09-01-2019 04:57 AM

Re: Multivalued fields in Sports Card Database
 
Quote:

Originally Posted by NauticalGent (Post 1638245)
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/a...les/4594431956.
Each has a similar structure to the one you read.
You may find something else that interests you


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

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