Choosing Primary and Foreign Keys

[A.V.H]

Help - I am trying to get my head around choosing Primary and Foreign Keys. I understand Primary in that I have chosen the Retailer I.D as my primary in the retailer table. Can this now become the foreign key when found in other tables or am I to look for a completely new field.

 

[tina]

your first idea is correct, rather than your second idea.
in order to link a specific retailer to a record in another table, copy the
RetailerID primary key field and paste it into the other table, where it is
considered a foreign key. fyi, one naming convention is to change the name
of the foreign key field to fkRetailerID in the "other" table, so it's
apparent that the field is a foreign key in that table.
*note*: if the primary key is an Autonumber field, change the foreign key
to a Number field with Field Size as Long Integer. also suggest you then
delete the Default Value (0) of the foreign key.

hth

Help - I am trying to get my head around choosing Primary and Foreign

Keys. I understand Primary in that I have chosen the Retailer I.D as my
primary in the retailer table. Can this now become the foreign key when
found in other tables or am I to look for a completely new field.

 

[A.V.H]

Thanks that helps -
So Foreign Keys are Primary keys that appear in other tables? And what happens when 2 tables hold the same primary Key or is this even possible? would it just become a 1 - 1 as against a 1 - many?

 

[Jeff]

I have a similar issue - where I have 3 tables,
tblShop
created Shop ID ( Primary key
Shop Nam
Shop Addres
Stat

tblPurchase
created Shop ID (Primary Key
Produce Cod
Quantity purchase
Purchase dat

tblproduce
Produce code (Primary Key
Produce nam
Produce Descriptio
wholesale cost

As you can see I am having problems deciding which to be the primary and which to be the foreig

 

[tina]

So Foreign Keys are Primary keys that appear in other tables?
yes, for the purpose of linking the records in two tables together.

And what happens when 2 tables hold the same primary Key... would it just
become a 1 - 1 as against a 1 - many?

correct again - if two tables share a primary key, that is a one-to-one
relationship. it's not often used, perhaps occasionally to protect sensitive
data that would otherwise appropriately be placed in one table. for
instance, a table containing customer records might include a field for
credit card number - one CC# per customer. to further protect the CC#, you
might instead place that field in a second table with the same primary key
as the customer table, preserving the "one CC# per customer" rule, and allow
very few people access to the second table.

hth

Thanks that helps -
So Foreign Keys are Primary keys that appear in other tables? And what

happens when 2 tables hold the same primary Key or is this even possible?
would it just become a 1 - 1 as against a 1 - many?

 

[tina]

see the suggested tables below. note the standardized table and field names,
most importantly *removing the spaces in the names*. see the following link
for further info:
http://www.mvps.org/access/tencommandments.htm
btw, these tables are not fully normalized. i would probably break
tblPurchases into two tables, but i would have to know more about the
process the database is supporting before i would make a specific
recommendation.

tblShops:
ShopID (primary key)
ShopName
ShopStreet
ShopCity
ShopState
ShopZip

tblPurchases:
PurID (primary key)
fkShopID (foreign key from tblShops)
fkProduceCode (foreign key from tblProduce)
PurQuantity
PurDate

tblProduce:
ProduceCode (primary key)
ProduceName
ProduceDescription
WholesaleCost

hth

 

[A.V.H]

Thanks so much -very helpful

 

[rkc]

see the suggested tables below. note the standardized table and field names,
most importantly *removing the spaces in the names*. see the following link
for further info:
http://www.mvps.org/access/tencommandments.htm
btw, these tables are not fully normalized. i would probably break
tblPurchases into two tables, but i would have to know more about the
process the database is supporting before i would make a specific
recommendation.

tblShops:
ShopID (primary key)
ShopName
ShopStreet
ShopCity
ShopState
ShopZip

tblPurchases:
PurID (primary key)
fkShopID (foreign key from tblShops)

<snip>

Why not fktblShopsShopID?

 

[tina]

personally, i never include a table name within a field name, too long and
confusing. i wouldn't want to have to type a name that long and hard to read
again and again - in code, in expressions, etc.
also, the most important element in using any naming convention, in any one
database, is consistency. if i used fktblShopsShopID for one foreign key
field, then i should use the same combination of "parts" for all foreign key
fieldnames in my database. the could lead to even longer, yuckier names.
to any developer who does a quick review of the table names, it's going to
be pretty apparent that fkShopID "probably" refers back to tblShops. that's
the important thing. you want clarity and consistency for yourself as a
developer (who may come back to the database 6 months later, not remembering
anything about it!). and just as importantly, for your customer, you want
some degree of clarity and consistency to help the *next* developer make
heads-or-tails of your design.
but you can name your tables and fields anything you want. naming
conventions are guidelines, not laws.