Normalization?

[CribbsStyle]

'm new to access and just read about normalization. I'm creating a
Sports Card Manager application. My question is how would I normalize
my database. Right now I have one table labeled cards, with all of the
fields in it, like one big spreadsheet. Here are the fields...

id
Own/Want (Own/Want)
Sport (Baseball/Basketball/Football)
year
manufacturer
set
Player Name
Card #
serial #
forsale
saleprice
fortrade
description
cardtype (Base/Insert/Parallel/Other)
autographed (Yes/No)
gameused (Yes/No)
pressplate (Yes/No)
raceused (Yes/No)
rookie (Yes/No)
value
fimage

 

[Pat Hartman]

Names should not include special characters or spaces so change the #'s and
spaces in your names. Use CardNum and SerialNum instead. In addition id is
non-descriptive. Change it to CardID so you know what table it refers to.
You have also used at least three names that will cause problems in certain
situations - year, description, and value. Year is a function name, Value
and Description are property names. Avoid the use of names which Access or
SQL has already assigned a meaning to. Search help for reserved word list
if you have questions. Generally, simple, short words are very likely to
have already been taken and so you should avoid them. Rely on the reserved
word lists until you become more familiar with Access and SQL.

My domain knowledge is limited, not being a collector myself, but I think
that a card may show multiple players. If you will need to list them all,
you'll need a many-side table to hold player names. FirstName and LastName
are also a better choice than a single name field. In no case would it be
appropriate to mush multiple names together into a single field. You could
use the word "Team" in the last name field for the team cards if you don't
need to list individual players.

 

[CribbsStyle]

Like this...

Cards
----
CardID
Own/Want
CardNum
SerialNum
ForSale
ForTrade
Autographed
EventUsed
EventWorn
GameUsed
Graded
PressPlate
RaceUsed
Rookie
Value

Condition
----
ConditionID
Condition

CardYear
----
YearID
CardYear

Players
----
PlayerID
FName
LName

Sports
----
SportID
Sport

Manufactures
----
ManufID
Manufacturer

Sets
----
SetID
Set

Images
----
ImageID
Fimage
BImage
EImage


Now how do I link the tables together corectly? I'm completly new to
this

Also, thanks for all your help!

 

[roccogrand]

You may want to check out the data model for the Lahman Baseball Database on
the Ziff-Davis website (zdnet.com). It defines about 20 tables that baseball
card collectors could use in a well normalized database.

http://downloads.zdnet.com/thankyou...&compid=61291&docid=290296&view=290296&load=1

HTH

LDN

 

[John W. Vinson]

Comments inline....

Like this...

Cards

This could be a Yes/No field Want.

CardNum
SerialNum

Is SerialNum - or the combination of Manufacturer and SerialNum - a unique,
stable identifier? If so it could be the Primary Key.

ForSale
ForTrade
Autographed
EventUsed
EventWorn

What's an "event" in this context?

GameUsed
Graded
PressPlate
RaceUsed
Rookie
Value

Condition

But you don't have a ConditionID (or a Condition) in the Cards table. The
condition table is of no use if it's not linked to anything! I'd suggest just
having a few-row single field Condition table with appropriate values
("Unopened", "Excellent", ..., "Damaged") as a text PrimaryKey, and store the
text value in a text Condition field in the cards table.

CardYear

Unneeded. Just use an Integer CardYear field in the Cards table.

Players

Probably need a little more biographical information... and you DO want a
PlayerID field in the cards table, otherwise you won't be able to tell who's
on the card!!!

Sports

See Condition... same drill.

Manufactures

Need the ManufID in the Cards table... again.

Sets

How does a Card relate to Sets? Are all cards members of sets, or do some
cards come in sets and some don't?

Images
----
ImageID
Fimage
BImage
EImage


Now how do I link the tables together corectly? I'm completly new to
this

You need a Primary Key for each table; e.g. ManufID would uniquely identify
the manufacturer. And you need a "Foreign Key" - a field of the same datatype,
Long Integer if the primary key is an Autonumber - in the "many" side table.
Since each Manufacturer makes many Cards, you need a long integer ManufID in
the Cards table to specify who made that card. Same drill for the other
tables/fields.

John W. Vinson [MVP]

 

[CribbsStyle]

Comments inline....



This could be a Yes/No field Want.


Is SerialNum - or the combination of Manufacturer and SerialNum - a unique,
stable identifier? If so it could be the Primary Key.

Some cards have serial numbers like 017/500

What's an "event" in this context?

Event same as Game Used, but refers to like Wrestling Cards

But you don't have a ConditionID (or a Condition) in the Cards table. The
condition table is of no use if it's not linked to anything! I'd suggest just
having a few-row single field Condition table with appropriate values
("Unopened", "Excellent", ..., "Damaged") as a text PrimaryKey, and store the
text value in a text Condition field in the cards table.

Dont quite understand..."I'd suggest just
having a few-row single field Condition table with appropriate values
("Unopened", "Excellent", ..., "Damaged") as a text PrimaryKey, and
store the text value in a text Condition field in the cards table."

Unneeded. Just use an Integer CardYear field in the Cards table.


Probably need a little more biographical information... and you DO want a
PlayerID field in the cards table, otherwise you won't be able to tell who's
on the card!!!

Not Really interested in biographical info, but not a bad idea

See Condition... same drill.


Need the ManufID in the Cards table... again.

How does a Card relate to Sets? Are all cards members of sets, or do some
cards come in sets and some don't?

Well like the Manufacturer Upper Deck, has several sets, and subsets
for that matter..example...

Upper Deck Collectors Choice
Upper Deck SP
Upper Deck SP Prospects
etc...

 

[CribbsStyle]

Also, why do it this way, instead of just like a spreadsheet? What are
the advantages?

Thanks

 

[John W. Vinson]

Some cards have serial numbers like 017/500

No problem. Use a Text(20) field. If you assume that a Primary Key must be an
Autonumber you've been misled by Microsoft's propaganda; a PK can be any
datatype (I'd avoid Float, Double or Date just because of precision problems)
except Memo.

Event same as Game Used, but refers to like Wrestling Cards

Use one field then - a game is an event. Better than having "basically the
same" info in two fields. What's the distinction between Used and Worn then???

Another like GameUsed, EventUsed? Same deal.

Dont quite understand..."I'd suggest just
having a few-row single field Condition table with appropriate values
("Unopened", "Excellent", ..., "Damaged") as a text PrimaryKey, and
store the text value in a text Condition field in the cards table."

You're talking about a numeric ConditionID, values 1, 2, 3, 4, 5 - and a
separate Condition field values "Mint", "Excellent", "Good", "Minor wear" etc.
You save a *few* bytes by using a numeric field, but not enough to really
bother. Just store the actual text condition in your Cards table, and use a
single-field Conditions table to validate that you have the right condition,
and as the row source of a combo so you can select it.

Not Really interested in biographical info, but not a bad idea


Well like the Manufacturer Upper Deck, has several sets, and subsets
for that matter..example...

Upper Deck Collectors Choice
Upper Deck SP
Upper Deck SP Prospects
etc...

But some manufacturers *don't* have Sets? Again: do some cards come in sets
and some don't?

Perhaps you could have a table of Sets, and an *optional* Set field in the
Cards table.

Front, back, extra...??? will there EVER UNDER ANY CIRCUMSTANCES be four
images?

I'd actually use one image per record:

Images
CardID <<< link to Cards, what card is this image of
ImageType <<< "Front", "Back", "other"
Image

Did you get the reiterated point about storing a foreign key in the Cards
table for each type of data?

John W. Vinson [MVP]

 

[John W. Vinson]

Also, why do it this way, instead of just like a spreadsheet? What are
the advantages?

Thanks

Consistancy of your data, mainly. If you have eight cards for that obscure
outfielder "Reginald Alberswaith", and have his name in the Players table
once, then you won't have the risk of havig his name spelled six different
ways on the eight Cards records. If a manufacturer changes their name (through
a merger, say) then you can correct the name only once, in the Manufacturers
table, and have the change instantly apply to all the cards (the cards table
has manufacturer 31, and all the 31's will point to the correctly spelled
name). You don't have to type in the manufacturer's name for every card, just
pick it from a combo box. There are all SORTS of advantages.

John W. Vinson [MVP]

 

[John W. Vinson]

You may want to check out the data model for the Lahman Baseball Database on
the Ziff-Davis website (zdnet.com). It defines about 20 tables that baseball
card collectors could use in a well normalized database.

Cool. Thanks for the link - this is NOT an area where I have any expertise, as
may have been evident!

John W. Vinson [MVP]

 

[CribbsStyle]

Perfect, I've got it now.

One more question, I created a table like this....

Players
-------------
PlayerID
First Name
Last Name


Now if the card has 2-3 players on it, how do I work this out?

 

[john]

You may want to check out the data model for the Lahman Baseball Database
on
the Ziff-Davis website (zdnet.com). It defines about 20 tables that
baseball
card collectors could use in a well normalized database.

http://downloads.zdnet.com/thankyou...&compid=61291&docid=290296&view=290296&load=1

Also check out http://www.rdaworldwide.com/pdox/datanorm.pdf, an excellent
starters article about normalization. With one example where all the
normalization rules are shown.

john

 

[John W. Vinson]

Perfect, I've got it now.

One more question, I created a table like this....

Players
-------------
PlayerID
First Name
Last Name


Now if the card has 2-3 players on it, how do I work this out?

You need another table to model the many to many relationship:

Cards
CardID
<other fields, *NOT* including PlayerID>

Players
PlayerID
<...>

PlayersOnCards
CardID <link to Cards, which card are you talking about>
PlayerID <link to Players, who's on this card>
<maybe some other fields, such as left to right position, etc.>

A typical card with only one player will have only one record in this table.
If a card has three players, there'd be three records for that CardID.

John W. Vinson [MVP]

 

[CribbsStyle]

If I make a form so you can enter information, how would the text
boxes to type in the first name and the last name be bound?

 

[CribbsStyle]

Anyone?

 

[John W. Vinson]

If I make a form so you can enter information, how would the text
boxes to type in the first name and the last name be bound?

You could do this several ways. Needing to update both the PlayersOnCard and
the Players table simultaneously makes it a bit more complex, but you should
be able to use a Form based on Cards and a Subform based on a query, joining
Players to PlayersOnCards. Be sure to include the PlayerID from both tables in
the query.

Alternatively, use a subform with a Combo Box for the playerID (storing the
ID, displaying the name). Use the combo's Not In List event to pop up a player
entry form if the user starts to type in a player that isn't yet represented.

John W. Vinson [MVP]

 

[CribbsStyle]

OK, Now with team names....

On my Form I'm using an Option Group that allows user to select the
sport, and a dropdown box that allows user to select team.

Now how can I get it to only show the team names from the sport the
user choose in the option group?

 

[John W. Vinson]

OK, Now with team names....

On my Form I'm using an Option Group that allows user to select the
sport, and a dropdown box that allows user to select team.

Now how can I get it to only show the team names from the sport the
user choose in the option group?

Use a Query as the rowsource for the combo box; it would be based on the teams
table (which I presume has a field for the sport). As a criterion on the
sportID put

=[Forms]![YourFormName]![YourOptionboxName]

Requery the combo in the afterupdate event of the option group.

John W. Vinson [MVP]

 

[John W. Vinson]

If I make a form so you can enter information, how would the text
boxes to type in the first name and the last name be bound?

Base the combo on a Query on the players table. In the query put in a
calculated field such as

PlayerName: [FirstName] & " " & [LastName]

or

PlayerName: [LastName] & ", " & [FirstName]

depending on whether you want to type "Babe Ruth" or "Ruth, Babe".

The combo should also contain the PlayerID as the bound column, set to zero
width in the ColumnWidths property.

And per your "anyone" followup... patience, please. We're all unpaid
volunteers here. I check the groups frequently (too frequently for my
consulting business in fact) but it's not a chat room.

John W. Vinson [MVP]