Do multivalued fields in baseball card database go against normalization?

[cmo187265]

I am creating a sports card database and actually have another thread on this site about my database already but thought I should start a new thread since this is a different question.

I have determined that each sports card I own will have it's own record in a CardsTBL with fields pulling information from other normalized tables including a PlayersTBL.

My question comes as I realize some cards will have 2-4 players on the card, and team cards that show an entire team could have 9-22 players on the card face.

I still need to figure out how to set up a multivalued field but wanted to ask beforehand if this is an appropriate way to accommodate a Players field in the CardsTBL? Will I run in to issues down the road? Is it against rules of normalization?

It doesn't feel right to me to have a multi-valued field for some reason but can't think of another way to accommodate multiplayer cards.

Appreciate any feedback as I am still very new to this!

[NeoPa]

I can't say I have much experience directly with MVFs Chris, but the rumble is that they're best left alone if you want to avoid extra problems.

Generally speaking it's 'better' to link multiple records in using a linking table rather than having a set number of FK links in the same record. I believe we covered use of FKs in a previous thread (Transaction table to update stock table?).

Linking Tables
Such tables allow links to be establised between differing tables (Again in more complicated scenarios also between multiple records in the same table but we'll ignore that for now). A Transaction table could be considered to be such a table. Keeping it very simple we can assume a Transaction links an item with a buyer. A Transaction record would have a link to both tables in the record as well as possibly other data. There could be multiple transactions for the same pair of items - say if a buyer buys the same item two weeks in a row. There is no limit on what can be linked.

All that said, this gives you enormous flexibility to handle links between items but it can make queries harder to maintain as updatable (Reasons for a Query to be Non-Updatable) so care is often needed on that score.

[zmbd]

IMHO Avoid the multivalue field
They are difficult to query etc... etc... etc... there are a few threads here on Bytes.com and other sites that discuss the evils of these fields.

What these fields do is hide the normalization of the data in system tables.

The team cards do create a bit of a kink in your database from https://bytes.com/topic/access/answe...le#post3819211
You may need to normalize tblCards further by pulling the player field and team field from that table and create another join table so that
[pk_JoinCardPlayerTeam][FK_tblCards][FK_tblPlayer][FK_Teams]
(PK = Primary Key, FK = Foreign key)
You should have a compound key on the three [FK_*] so that that you can't duplicate entries.
(Yes, you could use the compound key as a natural primary key; however, when writing queries and code I find them to be a bit of a pain to use)

The queries and forms would have to be updated to account for the new table

[cmo187265]

Thank you both for your replies and unfortunately confirming what I already suspected with MVFs.

Neopa: I appreciate the refresher on linked tables and admittedly will need to understand better. I wanted to have a better understanding of my specific problems before proceeding further down that rabit hole.

zmbd: I am afraid I need to chew on your suggestion a bit because I am slow to grasp this stuff. I will re-read several times and perform additional searches on what I don't understand.

It seems based on a previous post that you had some knowledge of the baseball card hobby. I just want to make sure your suggestion above was made with you understanding my dilemma with team cards, yes?

Dilemma:
I may have a collector who is looking for Randy Johnson cards and another looking for Ken Griffey Jr. cards. I would need for a Mariners team card (one record) with both players to be available in a search under either/both player names Griffey and Johnson.

I have no forms or queries built...just painfully planning tables lol.

[NeoPa]

CMO187265:
NeoPa: I appreciate the refresher on linked tables and ...

Perhaps I should have chosen a different term. Be very careful of confusing such link-tables with linked tables. The latter are tables used in Access that link to data stored elsewhere (Such as other Access tables or SQL Server; Oracle; etc).

If you need further help then feel free to post another question. You're doing very well so far keeping your questions separate and explaining them fully. Top marks.

[cmo187265]

Neopa: when you say link-tables or linking tables, are you talking about what is also called joins where I would need to understand how inner and outer joins work and create a join table that joins two tables with many-to-many relationships (in my case)? Thanks for your help. Still working on this.

[NeoPa]

Hi again.

I'm not sure you could say linking tables are synonymous with JOINs but they're certainly related.

It's possible (and most common after all) to use JOINs between tables without a linking table anywhere in the structure. However, when linking tables are used they certainly link using JOINs. Typically these are set up as Relationships, which causes Access to create JOINs automatically between any two tables whenever they appear in a Query.

Let me (hopefully) clarify :
A linking table is one that allows links to be defined between two other tables in a many-to-many relationship. Each record defines one related pair.

Consider people on the one side and buildings they frequent on the other. A table of people is fine. A separate table of buildings is also fine. Now you need a way of saying which people frequent which buildings. A typical family will have a home, but also a place of work for one or both parents and possibly schools for the children.

A Field in the People table won't be adequate because most frequent >1 building. That's even more extreme the other way round. A building would need to handle tens or more people. This is where the linking table comes in. Each record has a single Field to indicate a person and a separate single Field to indicate a building. Thus each and all can be matched accurately.

[cmo187265]

I have created two table design plans in attempt to solve my dilemma as stated above. The 1st didn't seem to work for me or I couldn't figure it out.

Attempt 1:
CardTBL
CardID-autonumber (PK)

TeamTBL
TeamID-auto (PK)

PlayerTBL
PlayerID-auto (PK)

CardPlayerTBL
CardPlayerID-auto (PK)
CardID (FK)
Player (FK)

CardTeamTBL
CardTeamID-auto (PK)
CardID (FK)
TeamID (FK)

Attempt 2: (see attached image)
I followed zmbd's suggestion and can see that I am able to "link" all of the tables to accommodate 1 card with multiple players, 1 player with multiple cards, 1 player with multiple teams, 1 card with multiple players and multiple teams, etc through these subdatasheets access inserted. Before I start a new question on subdatasheets I want to ask if the image attached is conceptually correct? Will it cause me issues in the future?

Thank you all for your help.