Problem with a surrogate key?

Question

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?

Answer 1

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.

Answer 2

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.