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.

The table you point into must have a column whose value is unique for each record and never changes. Such a column is called a primary key. The courses table already has one, because the university assigns course numbers for this purpose.

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, since two people do not share one address. But one of the requirements says that an administrator must be able to update a professor’s name or email address. Suppose every offering points at its professor by email, and then a professor changes their email address. Now the offerings point at an address that is no longer in the professors table, and fixing them means visiting every offering that professor has ever taught. That is the same problem we split the tables to avoid.

So a primary key needs two properties. Its value must be unique, and its value must not change. When the data has no column like that, 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 has no primary key of its own. It does not need one, because the combination of student ID and offering ID is unique. No student can take the same offering twice, so no two rows can have the same pair of values. The database can enforce that rule, and it can use that pair of columns as a primary key.

In this design, every fact from the original spreadsheet row is now stored exactly once. The course number, title, credits, and requirement label 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. Every fact from the original spreadsheet row is reachable.

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.