How to prevent redundant data that is hard to detect, e.g. John Smith

Question

I have a database table to store technicians (employees) which is structured like this:

| firstName | lastName | phone | alternatePhone | email | canadaTravel | canDrive | notes |

In this table, only the first two columns are required. Any other values can be null. I would like to prevent against duplicate employees, but I realize this key cannot be made based on name; There may be two John Smiths that we hire.

My initial thought was to expand a key to (first, last, phone) or (first, last, email) but because those are nullable fields, it is not guaranteed to be unique.

Is there a way to design the table to allow for two different people with the same name, but prevent the same person being entered twice?

Answer 1

There is no way to allow for two technicians with the same name and still prevent duplicate technician's from being entered without always providing an additional characteristic that will differentiate them to the DBMS as part of a unique constraint. The DBMS does not reflect reality but instead only what you have asserted to it about reality. It cannot determine if two technician's are really the same technician. Instead it can only determine if the data entered is consistent with the constraints declared to it.

Therefore, if you want to be able to enter two people with the same name you must provide additional characteristics (columns) that you require upon entry so that the DBMS can check their values against existing values and differentiate. You are thinking along the right lines - adding phone number or email. But to do so those columns must become required. Short of doing this there is really no other way.

Answer 2

Perhaps you will solve this problem with little help from your UI person. Check for the presence of the name in the DB before making a new entry. If similar name is found in the DB, show some kind of warning in the UI that the user should be able to override.

Otherwise, as you perhaps realize, you can either have a unique constraint in the DB or not.

Answer 3

Use the employee's Social Insurance Number (Canadian) or the US equivalent. Do you display this value, but use it to uniquely identify the person in your employee table. Assign a unique EmployeeID (generated primary key) and use this value to identify the employees.