Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design

Why Relationships Are Important

A relationship is an important component of a relational database.

• It establishes a connection between a pair of tables that are logically related to each other. A pair of tables is logically related via the data each contains. For example, consider the tables in Figure 10.1

  Figure 10.1. A pair of logically related tables.

  A logical relationship exists between the data in the STUDENTS table and the data in the STUDENT INSTRUMENTS table. A student can check out one or more instruments during the course of a school year, so a record in the STUDENTS table (representing the student) can be related to one or more records in the STUDENT INSTRUMENTS table (representing the particular instruments the student checks out).

• It helps to further refine table structures and minimize redundant data. As you establish a relationship between a pair of tables, you will inevitably make minor modifications to the table structures. These refinements will make the structures more efficient and minimize any redudant data that the tables may contain.

• It is the mechanism that enables you to draw data from multiple tables simultaneously . In Chapter 12, you'll learn how a relationship enables you to construct a view using fields from two or more related tables.

A properly defined relationship ensures relationship-level integrity, which guarantees that the relationship itself is reliable and sound. (Recall that relationship-level integrity is a component of overall data integrity.) You can take advantage of the many benefits a relational database provides only when you establish each relationship carefully and properly. Failure to do so means that you'll have a hard and tedious time working with data from multiple tables, and you'll certanly encounter problems when you try to insert, update, or delete records in related tables. You'll learn more about these types of problems later as the design process unfolds.