Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
1

I am not sure if this question has been asked or not. At least I couldn't find it.

I am curious about a primary key in terms of efficiency with data searching and retrieval.

This is a hypothetical example... I have a list of students with unique StudentID (say 10 digits long, given that the school will never have that many students) and StudentName (which is unique too).

Would it be better to use StudentID as primary key or create a new field for primary key (may be 6 - 8 digits or composite of characters and numbers. i.e. ATC1002)?

If I use StudentName as the primary key, will I see any retrieval performance degradation? If so, approximately how much in general? Are there any other factors which will have an impact on the selection of the primary field?

share|improve this question
It would help if you could provide more background about the specific environment. For instance, how many records are you expecting in the Students table? The average school has less than 10,000 students which is a trivial amount of records for any modern DBMS. However, if you are talking about hundreds of millions of students, then choosing the correct key is extremely important, and I would certainly not use StudentName as the key field, not least because it will certainly not be unique! – Max Vernon Sep 10 '12 at 18:27
I understand that any fine-tune or design question can be answered in "it depends".. I am not going for any particular environment in terms of hardware or amount of data. But if I must select then I would like to hear the answer for a large database in both live environment with constant updates and warehousing environment with only reads. – Bhrugesh Patel Sep 11 '12 at 1:07

1 Answer

up vote 9 down vote accepted

You should never assume that a data point which is outside of the control of your system will never change. This means you shouldn't assume student names won't change. There are lots of reasons in the real world why names might change. Anything that is at reasonable risk of changing is a bad candidate for a primary key. Also, names are very unlikely to be unique over a student population of any reasonable size.

Some exceptions to this might be things which are controlled by an external standards body which can be reasonably well trusted to maintain consistency. This could include something the the IATA code for an airport or the symbol for an atomic element.

Regarding the efficiency of textual (natural) keys vs. integer (surrogate) keys, there is no easy answer to this as it depends on many factors. On balance, it is fair to say that surrogate integer keys are more efficient than natural textual keys - especially if your textual values are much larger than a few characters.

Nevertheless, there are advantages to natural keys beyond raw file I/O and CPU cycles, as long as you can trust the natural keys to be stable. A school's student ID is probably a pretty good candidate because you can institute an internal policy that says student IDs are granted for life and never change.

share|improve this answer
Yes, but I think that it's better to just assume that there will be more than one John Smith in a school, so let the old student IDs be relegated to history and allow the student ID to be reused at some point in the future... – jcolebrand♦ Sep 10 '12 at 18:37
1  
Also of note: The Social Security Number, while an important tracking metric for federal reasons, is NOT an ID. Do not use it as an ID. Ever. – jcolebrand♦ Sep 10 '12 at 18:37
@jcolebrand - Agreed re SSN. Regarding student IDs, the question becomes at what point in the future do you reuse a student ID? My university assigns a user ID for life. I attended in the 80's but if I go back for another degree when I retire I'll still have the same ID as when I was a frosh. While I suppose they could reuse my student ID after I die, there really isn't a good reason to do so. – Joel Brown Sep 10 '12 at 19:05
depends on how the StID is assigned. f_init + l_name + consecutive_aggregate_incr vs f_init + l_name[0] + l_name[1] + right(7,cast(autoid_field) as varchar(16)) etc./ – jcolebrand♦ Sep 10 '12 at 19:19
My point here is that it's valid to have jsmith01 every year. – jcolebrand♦ Sep 10 '12 at 19:24
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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