Take the 2-minute tour ×
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.

I am working with an application for students management. For the student's table I used a surrogate key (auto increment id). The problem I face with the surrogate key is when the user wants to show information about a student he must enter the id but the user knows only the name of the student (and I can't use the name as a primary key because it's not unique). How can I solve this problem?

share|improve this question
add comment

2 Answers

There are a few things of interest here (note: I currently work in this field, so I'm not going to go into too much depth with regards to system functionality and requirements).

  1. Avoid exposing database internal identifiers to the outside world. The field values in an integer IDENTITY column have no meaning outside the database. The absolute farthest they should go is the client-side data access layer.

  2. Normally, students (people) are identified within a jurisdiction by a unique number (i.e., student number) assigned by the jurisdictional authority (i.e., department of education). In some jurisdictions, students (people) need to exist in the system without a unique number because the numbers aren't assigned synchronously as students (people) are registered. I don't know if you need to deal with that or not. If that is the case, students (people) without the unique number are determined to be unique by a business key, such as the combination of first name, last name, gender and birth date. Everyone, though, will have a unique surrogate key value in the database, which you (not the user) will use to uniquely identify a record.

  3. You will have to implement functionality to search for students. Most of the time users of the system will be searching based on a name (or partial name), not a student number. The resulting search should display students to which the user "has access." For example, if the user is a teacher, they should only have access to the students they teach. The search interface will only allow the user to select a single student -- which will give you the internal identifier you need -- before proceeding.

So in summary, I think you're on the right path with the database design, it's just a matter of implementing some extra functionality on the front end for the user to be able to uniquely identify a student.

share|improve this answer
    
+1 but regarding "combination of first name, last name, gender and birth date", I guess there are at least 15K people with the same first and last name as mine. Naturally, many of them will be born on the same date. If I remember correctly, the chances that at least two students in a class of 30 have the same birthday are high, over 90%. –  AlexKuznetsov Mar 3 '13 at 21:26
    
@Alex: Yes, definitely; however, that key only applies when a student number is not yet assigned (if the numbers aren't assigned in real-time, usually it's done as a nightly batch), so it's much more rare to have duplicates (though still possible; as I said, this is not an in-depth answer). Perhaps my post didn't make that distinction clear enough, sorry. –  Jon Seigel Mar 3 '13 at 22:19
add comment

The short story is that you can't solve that problem. Not to everyone's satisfaction, anyway.

To distinguish one "John Smith" from another, you have to know and store and expose something besides John Smith's name.

A lot of web sites use an email address. Many government offices in the USA use a Social Security number (SSAN). The Department of Motor Vehicles uses a driver's license number. Colleges and universities use a student ID number. The US Postal Service (USPS) uses your mailing address.

If the purpose is to let, say, teachers look up information about their students, the student's id number should be on the teachers' class roster. Teachers generally don't have any business looking up student information for students that aren't on their roster. Other administrators can ask to see the student's ID card. Parents can be sent a letter with their children's id numbers in it.

share|improve this answer
add 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.