If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Choosing Primary and Foreign Keys
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.
|
#2
|
|||
|
|||
Choosing Primary and Foreign Keys
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 "A.V.H" wrote in message news 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. |
#3
|
|||
|
|||
Choosing Primary and Foreign Keys
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? |
#4
|
|||
|
|||
Choosing Primary and Foreign Keys
I have a similar issue - where I have 3 tables,
tblShop: created Shop ID ( Primary key) Shop Name Shop Address State tblPurchase: created Shop ID (Primary Key) Produce Code Quantity purchased Purchase date tblproduce: Produce code (Primary Key) Produce name Produce Description wholesale cost As you can see I am having problems deciding which to be the primary and which to be the foreign |
#5
|
|||
|
|||
Choosing Primary and Foreign Keys
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 "A.V.H" wrote in message ... 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? |
#6
|
|||
|
|||
Choosing Primary and Foreign Keys
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 "Jeff" wrote in message ... I have a similar issue - where I have 3 tables, tblShop: created Shop ID ( Primary key) Shop Name Shop Address State tblPurchase: created Shop ID (Primary Key) Produce Code Quantity purchased Purchase date tblproduce: Produce code (Primary Key) Produce name Produce Description wholesale cost As you can see I am having problems deciding which to be the primary and which to be the foreign |
#7
|
|||
|
|||
Choosing Primary and Foreign Keys
Thanks so much -very helpful
|
#8
|
|||
|
|||
Choosing Primary and Foreign Keys
"tina" wrote in message ... 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? |
#9
|
|||
|
|||
Choosing Primary and Foreign Keys
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. "rkc" wrote in message ... "tina" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|