Linking the Tables with Keys

We left the offerings table without a way to record which course it is an offering of. It records a term, a meeting time, and a room, but it does not record which course is being offered. The fix is to write the course number into the row:

That works because the course number names exactly one course. No two courses share a number, so EN.601.226 written in an offering row can only mean one row of the courses table. If we want to know the title of the course, we can follow the course number to the courses table and read it there. If we had included the course title in the offerings table instead, we would be relying on no two courses ever sharing a title, which is a weaker guarantee.

A primary key is a column, or combination of columns, that uniquely identifies each row and cannot contain null values. The course number is a natural choice for the courses table because the university assigns a distinct number to each course. A good primary key also stays stable, so other rows can keep referring to it.

Now try to find a primary key for the professors table.

The name will not do. Two professors can share a name, and if a second Sara More joins the department, every offering that points at “Sara More” becomes ambiguous. Nothing stops that from happening.

The email address is a better candidate if each professor has a distinct address. But one of the requirements says that an administrator must be able to update a professor’s name or email address. If every offering points at its professor by email, changing that email also requires updating the references in the offerings. The database can coordinate those changes or reject an update that would break the links, but choosing a stable ID avoids the need to change those references.

So we want a primary key that uniquely identifies each row and has no reason to change. When the existing data offers no suitable column, we add one:

The professor ID is set by the database (or the application that uses it) and it means nothing outside the database. It has no reason to change, because it describes nothing that could change. A professor can change name, email address, or department, and the ID stays 3.

The offerings table needs an added ID column as well, for the same reason: a student’s history entry has to point at an offering. With both keys in place, the offerings table can be written down in full:

Notice there are three kinds of columns in this table.

• Term, meeting time, and room describe the offering itself.
• The offering ID is a primary key for this table, and it is there to make each record uniquely identifiable the same way the course number is a primary key for the courses table and the professor ID is a primary key for the professors table.
• Course number and professor ID do not describe the offering at all. They hold the primary key of a row in another table, and their whole purpose is to point there. A column used this way is called a foreign key.

A foreign key allows a relational database to establish a relationship between two tables. The offerings table has a foreign key into the courses table, and it has a foreign key into the professors table. That is how we can say that an offering is of a course and is taught by a professor.

More importantly, a foreign key allows the database to enforce referential integrity. If the application writes a course number into an offering row, the database can check that the courses table has a row with that number. If it does not, the database can refuse to store the offering row. The same goes for the professor ID. If an offering row carries a professor ID that does not exist in the professors table, the database can refuse to store it.

Since the database can enforce referential integrity, the application does not have to. It does not have to remember to check that the course number and professor ID exist before writing an offering row.

The students and their history entries work the same way. Let’s first write down the students table:

Now how do we record that a student took an offering? We could add a column to the students table and write a list of offerings into it. A student can take many offerings, so the column would have to hold a list of values. This could quickly answer the question of which offerings a student took, but it would require much more work to answer “whether a particular student took a particular offering,” and even more work to answer “which students took a particular offering.” To answer the latter question, we could add a column to the offerings table and write a list of students into it. Now we have two columns that need to be kept in sync, and we have duplicated the same information in two places.

The way to avoid that is to create a new table to hold the relationship between students and offerings, and it could be as simple as this:

That last table does not fit the description of a table we started with. Earlier we said that a table holds an entity, a real-world thing such as a student or a course, and that each row is one record of that thing. A history entry is not a thing in that sense. Each row here holds two foreign keys and nothing else. What the row records is that a student took an offering.

That is a legitimate table. The earlier description was a starting point, and it covers most tables, but it does not cover every table. Some tables exist to record that two things go together. Read the three rows above and you can see both directions at once. Student 1 took two offerings. Offering 3 was taken by two students. A table like this is called a junction table because it joins two other tables together.

Notice this too: the history entry table does not need a separate ID column. The combination of student ID and offering ID serves as its primary key. No student can take the same offering twice, so no two rows can have the same pair of values. The database enforces that rule through this composite primary key.

In this design, the facts we have placed so far are each stored where they belong. The course number, title, credits, and subject area are stored in the courses table. The professor’s name and email address are stored in the professors table. The term, meeting time, and room are stored in the offerings table. The student’s name and email address are stored in the students table. And the fact that a student took an offering is stored in the history entry table.

Also, if we ever want to create the original view of a student’s course history, we can do it by joining the tables together. We can start at the history entry table, follow the offering ID to the offerings table, follow the course number to the courses table, and follow the professor ID to the professors table. Following these links brings the student’s history entries together with the offering, course, and professor details.

One thing from that spreadsheet is still missing from the model. A course can count toward several degree requirements, and we have not written that down yet. Before moving on, look at the history entry table again, because we will use the same shape for that.