Table Relationships - Database Design for Mere Mortals - page 293

cord in STUDENT CLASSES can be associated with only one record in
CLASSES.
• The STUDENT CLASSES linking table contains two foreign keys. S TUDENT ID and
C LASS ID are both copies of the primary keys from the STUDENTS and
CLASSES tables, respectively; therefore, each is a foreign key by definition. As
such, they help to establish the relationship between their parent tables and the
linking table.
• The STUDENT CLASSES linking table has a composite primary key composed of
the S TUDENT ID and C LASS ID fields. Except in rare instances, a linking table al-
ways contains a composite primary key. (This rule applies to the database's logical
design only. There are various reasons why you might break this rule when you
transform the logical design into a physical design, but this is a discussion that is
beyond the scope of this topic.) It's important to note that you'll occasionally have
to add more fields to the linking table in order to guarantee a unique primary key
value. For example, assume the school decides to record student schedules for
every term of the school year (fall, winter, and spring). You would have to add a
new field, perhaps called T ERM , and designate it as part of the composite primary
key. This would enable you to enter another instance of a given student and class
into the table, but for a different term; a student may need to retake a class during
the spring term because he failed the class in the fall term.
• The linking table helps to keep redundant data to an absolute minimum. There is
no superfluous data in this table at all. In fact, the main advantage of this table
structure is that it allows you to enter as few or as many classes for a single student
as are necessary. Later in the database design process, you'll learn how to create
views to draw the data from these tables together in order to present it as meaning-
ful information.
• The name of the linking table reflects the purpose of the relationship it helps estab-
lish. The data stored in the STUDENT CLASSES table represents a student and
the classes in which he or she is enrolled.
As you work with many-to-many relationships, there will be instances in which you will
need to add fields to the linking table in order to reduce data redundancy and further re-
fine structures of the tables participating in the relationship. For example, assume you're
working on a new database with a colleague and he's just brought the ORDERS and
PRODUCTS tables in Figure 10.41 to your attention.