- The Importance of Good Database Design
- Types of Table Relationships
- Understanding Normalization
- Following the Design Process
- Summary
- Q&A
This chapter is from the book
This chapter is from the book
This chapter is from the book
Types of Table Relationships
In Hour 2, you learned that keys are used to tie tables together. These relationships come in several forms:
One-to-one relationships
One-to-many relationships
Many-to-many relationships
For example, suppose you have a master employees table for employees, containing their Social Security number, name, and the department in which they work. You also have a separate table containing the list of all available departments, made up of a Department ID and a name. In the employees table, the Department ID field will match an ID found in the departments table. As you've learned by now, this is a type of relationship, which you can see in Figure 3.1. The "PK" next to the field name stands for primary key, which you'll learn more about during this lesson.
){kind=link}
Figure 3.1 The employees and departments tables are related through the DeptID.
The next few sections will take a closer look at each of the relationship types.
One-to-One Relationships
In a one-to-one relationship, a key will appear only once in a related table. The example of the employees and departments tables is not a one-to-one relationship, as many employees will undoubtedly belong to the same department. An example of a one-to-one relationship is if each employee is assigned one computer within a company. Figure 3.2 shows the one-to-one relationship of employees to computers.
){kind=link}
Figure 3.2 One computer is assigned to each employee.
The employees and computers tables in your database would look something like Figure 3.3, which represents a one-to-one relationship.
){kind=link}
Figure 3.3 One-to-one relationship in the data model.
One-to-Many Relationships
In a one-to-many relationship, keys from one table will appear multiple times in a related table. The example shown in Figure 3.1, indicating a connection between employees and departments, is an example of a one-to-many relationship. A real-world example would be an organizational chart of the department, shown in Figure 3.4.
){kind=link}
Figure 3.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 friends 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.
Many-to-Many Relationships
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 opposite is also true, meaning that the primary key from that second table can also appear many times in the first table.
Think of it 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 will usually take more than one class at a time, and a class will always contain more than one student, as you can see in Figure 3.5.
){kind=link}
Figure 3.5 Students take classes, classes contain students.
As you can see, this sort of relationship doesn't present an easy method for relating tables. Your tables could look like Figure 3.6, seemingly unrelated.
){kind=link}
Figure 3.6 The students table and the classes table, unrelated.
In order to make the theoretical many-to-many relationship, you would create an intermediate table, one that sits between the two tables and essentially maps them together. You might build one similar to that in Figure 3.7.
){kind=link}
Figure 3.7 The table students_classes_map acts as an intermediary.
If you take the information in Figure 3.5 and put it into the intermediate table, you would have something like Figure 3.8.
){kind=link}
Figure 3.8 The table students_classes_map populated with data.
As you can see, many students and many classes happily co-exist within the students_classes_map table.
With this introduction to the types of relationships, learning about normalization should be a snap!