2

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

  • 1
    I've always thought schools provide you with some number. If they don't, Kenneth's answer makes perfect sense, no need to overthink this.. – Marian Dec 30 '13 at 0:22
3

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.

|improve this answer|||||
  • Why does a "large" primary keys (and the resulting foreign keys) make the system "heavily de-normalized"? If a PK is 5 columns, then it's 5 columns. That has nothing to do with de-normalization. – a_horse_with_no_name Dec 30 '13 at 1:21
  • Perhaps not technically de-normalized, but it would break DRY principles. – Max Vernon Dec 30 '13 at 4:08
  • @a_horse_with_no_name Normalization has never been my strong suit but my understanding is that if I have a key of FirstName, LastName, Email on my Student table, and then have to repeat that information in my Classes table then I'm duplicating data in that table and breaking one of the normal forms. Is that not correct? – Kenneth Fisher Dec 30 '13 at 16:58
  • "Repeating" the values of PK is not considered de-normalized. If the PK is really that big, that's the way it is. But (firstname, lastname, email) is not a good PK anyway (because the values of a PK should never change) – a_horse_with_no_name Dec 30 '13 at 19:03
  • "if I have a key of FirstName, LastName, Email on my Student table, and then have to repeat that information in my Classes table then I'm duplicating data in that table and breaking one of the normal forms. Is that not correct?" That is not correct. In any case it's a poor example because it seems highly unlikely that firstname and lastname would be part of a key for students in any real or practical sense. – nvogel Jan 4 '14 at 20:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.