Primary Key Question

Question

Building a database on Access 2007 and having trouble selecting the primary key. The tables consists of a student table and another table where the students are checked in. I would have liked to make the Student ID the primary key, but the check in staff doesn't request that on their paper-based forms. I also thought about the making the email the key, This would mean that they would only have to use their school provided emails

Please respond, perhaps a concatenated key would work?

NOTE: The information requested from students on the form when they sign in is there Name, Email, Date, Phone

Answer 1

If the school provided email is always going to be available and everyone is required to have one then you could certainly use that as the primary key. If not then it sounds like you don't have a good natural key. You might consider going with a surrogate key. Create an additional column that is auto incrementing and put your primary key on it. Then create indexes on the email and student id.

Lot's of people will argue that you should always use a natural key, and other's will argue that you should always use a surrogate key. In my personal opinion you should use a natural key where it makes sense and a surrogate when it doesn't. A natural key makes sense when it is reasonably short, consistent and always available.

I'm not a big fan of large concatenated primary keys. If nothing else when you link the table to another one you may have to include all of those columns to create a good join and that makes your system heavily de-normalized and your queries slower. At that point a simple integer surrogate key will let you create simple fast joins.