Recording Degree Requirements

One fact from the original spreadsheet row is still unplaced. Here is the requirement column from two of those rows:

EN.601.226    CS core
EN.601.220    CS core and FA2 Computing and Data Science

I settled part of this already. The requirement belongs with the course rather than with the offering, because EN.601.226 counts toward the same requirements whoever teaches it. So it could be a column in the courses table:

Read the first cell and you can see the problem. It holds two requirements, not one. This is the same problem you saw with a student taking several offerings. I showed what did not work: packing several values into one column makes it hard to ask questions about the individual values. Which requirements does Intermediate Programming satisfy? You have to read that cell and split it on the word “and”. If you do that, you may think it satisfies three requirements, because you have no way to know whether the second one is “FA2 Computing” or “FA2 Computing and Data Science”.

Now suppose you ask, “Which courses satisfy FA2 Computing and Data Science?” You have to search inside the text of that column in every row, and hope that every administrator typed the requirement the same way. One of them will write “FA2 Computing and Data Science” and another will write “FA2”, and the two rows will not match.

So as a general rule, a column should hold one value per row. If a fact has several values, I prefer to spread it across several rows rather than pack it into one. A course may satisfy several requirements, so I will record each requirement in its own row. To that end, degree requirements get their own table.

Notice I included an ID column. The name of a requirement may be unique, and if so, it could serve as a primary key. But a name is not guaranteed to stay the same. So I made a judgment call and added an ID column. The database can use that as a primary key, and the name can be changed without affecting any other table.

Now I need a junction table to link courses and requirements.

Read it in both directions. EN.601.220 appears twice, so Intermediate Programming satisfies two requirements. Requirement 1 appears four times, so four courses count toward CS core. Both questions are now answered by reading rows, not by searching inside text.

Notice that the two foreign keys in this table do not look alike. One holds a course number and the other holds an invented ID. That is fine. A foreign key holds whatever the table it points into uses as its primary key, and the courses table uses a course number while the requirements table uses an ID.

The primary key is the pair of columns again. No course counts toward the same requirement twice, so the combination of course number and requirement ID is unique.