Page 9 of 13PREVNEXT

Design the tables for a new database

Relationships bring your data together

Fig. 1  The key to relationships: Sharing your keys

We mentioned earlier in this course that after you break your data into tables, you marry it back together with links called relationships. Table relationships can become complex, and we'll address them in the third course in this series. For now, you need to plan them, and you do that by deciding where to put foreign keys.

A foreign key is simply a primary key that you use 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 foreign keys.

At this point, you may be thinking, "Hang on, sharing fields like that duplicates some data!" Don't worry, this kind of duplication is okay. Primary key values are small, and you can't extract 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 step in your design, indicate your foreign key fields.

You'll indicate primary and foreign keys during the practice, and links in the Quick Reference Card take you to more information about database design and relationships.

Page 9 of 13PREVNEXT