Page 8 of 11PREVNEXT

Design tables for a new Access 2007 database

Relationships bring your data together

The key to relationships: sharing your keys

So, you've divided your data into tables and listed the fields and primary keys. How do you relate your data back together? The answer to that question can become complex, and we'll address it in the third course in this series, but here are the basics.

You create relationships by using the primary key field from one table as a field in another table. The picture shows this. You can see how the primary keys in the Suppliers and Support tables have become fields in the Assets table. Those duplicate fields in the Assets table are called foreign keys.

At this point, you may be thinking, "Hang on, I've just duplicated some data!" Don't worry, this kind of duplication is okay. Primary key values are small, and you can't extract meaningful information from your database unless you use them in relationships. For example, the relationships in the picture allow you to answer questions such as, "Who supplied the monitors that we bought two years ago?" So, as a final step in your design, indicate your foreign key fields.

If you need more information about keys, relationships, or database design, see the links in the Quick Reference Card at the end of this course.

Page 8 of 11PREVNEXT