Restating a primary key to identify an employee

Question

Our company decided to designate a contractor by adding an "X" in front of the employee number. But they aren't doing this in all systems.

Is this a dangerous thing to do and why?

Answer 1

Without knowing more about their system I'd say that this is a bad design.

• If they want to look up an employee and they only have the employee number but they do not know if it is a contractor or not then they will have to check both with and without an X.
• The database probably allows a contractor and non-contractor with the same employee number because the unique constraint will allow it.
• Joining on REPLACE(employeenr, 'X', '') will work but it will be inefficient.

A better choice would be to make a column for contractor that can be 0 or 1. I guess there may have been reasons that prevented them from doing this (for example, legacy systems that cannot be changed).

Answer 2

Whether it is "dangerous" is a bit of a loaded question. It is doubtful people will die if you do this, however, I would definitely recommend against it. Fundamentally as a database designer, one of your overriding goals should be data integrity and that implies that each data value has a single meaning. A common statement that I tell my clients when they suggest doing something like this is "I cannot list every possible bad thing that could possibly happen by doing <insert bad practice here>, but I can state that there is a very high probability that it will create problems". In this case, that bad practice is trying impart multiple meanings on a single value.

Others have mentioned a few of the problems you might encounter. I know of a couple of others:

1. Validation of the employee number. You will have to alter your validation of the employee number (you are validating it right?) to overlook the leading character but only if it is an "X".
2. Reports/output to employees/contractors. Any report that shows the employee's number would need to remove the "X"

A better solution IMO, would be to add a column indicating the role of the person in the company: employee or contractor.