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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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).
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.
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.