MS Access

[erod]

I need a clear understanding on the one-to-many relationship. What role does the primary and foreign key play.

 

[Databaseguy]

Normalization. You don't want to store dupliucate information in a table.

For example. this table is not normalized

Name,Address,ProductBought
Joe,1234 Somewhere,Widget
Joe,1234 Somewhere,Thingamabob
Joe,1234 Somewhere,ChromePLatedPistonReturnSpring
Fred,3456 Shady Lane, Widget

Would be better with two tables. tblPerson and tblProductBought. Would be stored this way

tblProductBought
PersonID, ProductBought
1,Widget
1,Thingamabob
1,ChromePLatedPistonReturnSpring
2,Widget

tblPersonID
PersonID, name, Adress
1,Joe,1234 Somewhere
2,Fred,3456 Shady Lane

The primary Key would be personID in tblPerson. The Forein Key would be person ID in tblProductBought

Microsoft Press makes good books. However, my favorite is Access 2000 developers handbook by Letwin/Getz/Gilbert.

Also, go to help inside of access and search on Primary.

 

[tlbroadbent]

The primary key identifies the unique record in a table on the "one side" of the relationship. A foreign key is a key on a table that is the same as the primary key of another table. The foreign key represents the "many side" of the relationship. In this case the foreign key is non-unique.

A couple of examples may help. Suppose we have a part number table. Each part is identified by a unique key, part identifier - the primary key of the table. Suppose we also have a order item table showing all parts on a customer order. The order item table would contain the part identifiers - foreign keys - that related the order to parts on the part table. There could be many occurrences of the same part on the order item table.

We might also have an inventory location table that listed part inventory by location. Again the part identifier would be used as a foreign key and would be used in multiple or many records in the inventory location table. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.