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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-31-2019, 02:32 PM   #1
cmo187265
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.

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 by cmo187265; 08-31-2019 at 02:33 PM. Reason: Baseball DB changed to Sports Card DB (content)
cmo187265 is offline   Reply With Quote
Old 08-31-2019, 02:47 PM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,334
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
cmo187265 (08-31-2019)
Old 08-31-2019, 02:51 PM   #3
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,032
Thanks: 115
Thanked 3,017 Times in 2,744 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Multivalued fields in Sports Card Database

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


I know that you know that we both know nothing
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
cmo187265 (08-31-2019)
Old 09-01-2019, 01:13 AM   #4
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,019
Thanks: 450
Thanked 300 Times in 259 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Multivalued fields in Sports Card Database

Quote:
Originally Posted by isladogs View Post
Col,

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

Well done.
__________________
“It follows then as certain that night succeeds the day, that without normalization, we can do nothing definitive, and with it, everything honorable and glorious.” - with apologies to George Washington
NauticalGent is offline   Reply With Quote
Old 09-01-2019, 04:57 AM   #5
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,032
Thanks: 115
Thanked 3,017 Times in 2,744 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Multivalued fields in Sports Card Database

Quote:
Originally Posted by NauticalGent View Post
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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


I know that you know that we both know nothing
isladogs is offline   Reply With Quote
Old 08-31-2019, 03:14 PM   #6
cmo187265
Newly Registered User
 
Join Date: Aug 2019
Posts: 3
Thanks: 5
Thanked 0 Times in 0 Posts
cmo187265 is on a distinguished road
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.

Last edited by cmo187265; 08-31-2019 at 09:41 PM.
cmo187265 is offline   Reply With Quote
Old 08-31-2019, 08:56 PM   #7
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,032
Thanks: 115
Thanked 3,017 Times in 2,744 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
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)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


I know that you know that we both know nothing
isladogs is offline   Reply With Quote
Old 09-01-2019, 02:46 AM   #8
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,320
Thanks: 541
Thanked 941 Times in 892 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
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).
__________________
Code:
                 |||||
               @(~Ô^Ô~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Reply

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 05:11 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
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