Figure 14.1. The
and departments tables are
through the DeptID.
In the following sections, you will take a closer look at each of the relationship types.
In a one-to-one relationship, a key appears only once in a related table. The
tables do not have a one-to-one relationship because many employees undoubtedly belong to the same department. A one-to-one relationship exists, for example, if each employee is assigned one computer within a company. Figure 14.2 shows the one-to-one relationship of employees to computers.
Figure 14.2. One computer is assigned to each employee.
tables in your database would look something like Figure 14.3, which represents a one-to-one relationship.
Figure 14.3. One-to-one relationship in the data model.
In a one-to-many relationship, keys from one table appear multiple times in a related table. The example shown in Figure 14.1, indicating a connection between employees and departments, illustrates a one-to-many relationship. A real-world example would be an organizational chart of the department, as shown in Figure 14.4.
Figure 14.4. One department contains many employees.
The one-to-many relationship is the most common type of relationship. Another practical example is the use of a state abbreviation in an address database; each state has a unique identifier (CA for California, PA for Pennsylvania, and so on), and each address in the United States has a state associated with it.
If you have eight
in California and five in Pennsylvania, you will use only two distinct abbreviations in your table. One abbreviation represents a one-to-eight relationship (CA), and the other represents a one-to-five (PA) relationship.
The many-to-many relationship often causes problems in practical examples of normalized databases, so much so that it is common to simply break many-to-many relationships into a series of one-to-many relationships. In a many-to-many relationship, the key value of one table can appear many times in a related table. So far, it sounds like a one-to-many relationship, but here's the curveball: The
is also true, meaning that the primary key from that second table can also appear many times in the first table.
Think of such a relationship this way, using the example of students and classes. A student has an ID and a name. A class has an ID and a name. A student usually takes more than one class at a time, and a class always contains more than one student, as you can see in Figure 14.5.
Figure 14.5. Students take classes, classes contain students.
As you can see, this
of relationship doesn't present an easy method for relating tables. Your tables could look like Figure 14.6, seemingly unrelated.
Figure 14.6. The
table and the
To make the theoretical many-to-many relationship, you would create an intermediate table, one that sits between the two tables and
maps them together. You might build one similar to the table in Figure 14.7.
Figure 14.7. The
table acts as an intermediary.
If you take the information in Figure 14.5 and put it into the intermediate table, you would have something like Figure 14.8.
Figure 14.8. The
As you can see, many students and many classes happily coexist within the
With this introduction to the types of relationships, learning about normalization should be a snap.