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

I think it completely depends on how you implement it. I would rather just give them proper numbers and create a new table with numbers as foreign key and a row to state if he is a contractor or not .

Answer 2

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 3

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.

Answer 4

Very very poor idea. Inthe first place PKs should not, if at all possible, be anything except numeric for performance reasons. Second since this is now string data ordering is going to use string ordering and numeric ordering. This will really annoy people since 121 will follow 12 not 13!

A further difficulty in this scheme is that contractors sometimes become employees. You don't want to have to change all the related tables because his id number changed.

If someone wants to display the number with an X , then use a bit field to mark in the database if someone is or is not a comtractor, use an autonumbering field to get the employee number and use a case statement in the query to change 122 to X122 on the display if the person is a contractor.

Further performance will be affected because you cannot use an autogenerated number to get the employee number, this is almost alawys mistake because now you have to worry about race conditions.

Answer 5

What is the purpose of identifying a contractor in that way? If your company's payroll and HR systems don't have a better way to record that information then I'd say those systems are deficient and ought to be fixed or replaced.

Answer 6

better to add a new table that lists the consultants/contactors with a date range.