By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,969 Members | 1,579 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,969 IT Pros & Developers. It's quick & easy.

Do multivalued fields in baseball card database go against normalization?

P: 21
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!
2 Weeks Ago #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,347
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.
2 Weeks Ago #2

zmbd
Expert Mod 5K+
P: 5,380
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
2 Weeks Ago #3

P: 21
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.
2 Weeks Ago #4

NeoPa
Expert Mod 15k+
P: 31,347
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.
2 Weeks Ago #5

Post your reply

Sign in to post your reply or Sign up for a free account.