Naming the Relationships

I have built four relationships so far. Let’s look at two of them side by side.

A course points at one subject area. Many courses can point at the same subject area. The foreign key sits in the courses table, and there is no table in between.

A course can satisfy several requirements, and a requirement can be satisfied by several courses. Neither table can hold a single foreign key pointing at the other, so I built a third table just to hold the pairs.

Those are two different kinds of relationships, and they force two different structures. There is a name for each shape, and the name tells you where the foreign key goes.

One-to-many. One row on one side can be pointed at by many rows on the other side, but each of those rows points back at only one row on the first side. Subject area to courses is one-to-many: one subject area, many courses, each course pointing at exactly one subject area. Course to offerings is the same shape: one course, many offerings, each offering pointing at exactly one course. Professor to offerings, too.

Notice where the foreign key sits in every one of these. It sits in the table on the “many” side. The offerings table holds a course number and a professor ID, not the other way around, because one offering has one course and one professor, while one course and one professor can each have many offerings.

Many-to-many. Rows on both sides can point at several rows on the other side. A course can satisfy several requirements, and a requirement can be satisfied by several courses. A student can take several offerings, and an offering can be taken by several students. Neither table can hold the other’s foreign key, because we decided a column should hold one value, and one value cannot point at several rows. That is why courses and requirements needed the junction table, and why students and offerings needed the history entry table. A many-to-many relationship always needs a table of its own. That table holds nothing but the two foreign keys, and it sits between the two tables it connects.

There is a third kind of relationship, which I have not used yet. It is called one-to-one. Each row on one side points at exactly one row on the other side, and each row on that other side points back at exactly one row on the first side.