I have been using payroll employee ID in most of my applications and database tables as the unique identifier.
Recently our company switched to a new payroll system, and now a new employee ID is assigned for all employees.
Existing employees will have both IDs, but new employees will only have the new ID.
I'm struggling with how to update my existing applications to accommodate this change (e.g. how do I add a new entry to my existing table with the new ID: should I create a new column? Use the existing column?).
I have a few hundred tables/applications with the old ID embedded in them, so it's not trivial to update them all.
The new employee ID also has a different data type from the old one (Character vs Integer).
Any suggestions or ideas will be greatly appreciated!
This is an unfortunate outcome, but it definitely happens. You have my condolences.
I work at a large university, and we have a few systems that like to use different unique identifiers for people. We typically use a bridge table to deal with this. In essence, this is a simple table that allows for conversion between two or more identification systems.
Our primary ERP issues its own unique identifier, and all others are translated back and forth using the bridges. It sounds like your system wasn't generating its own unique identifiers, and was instead relying on an external system to do so. I'd argue this was a mistake.
That said, there's no technical reason you can't generate IDs like you have been in the past and maintain both going forward. Even if you don't have the older system, you know the format and can likely duplicate it with minimal effort.
Running this dual id system adds a little complexity, but if you add another product that tries to put yet another ID system... it may be worth it.
I'm sorry my answer is vague, but there's a lot of edge cases that could influence things several either direction here.
I would suggest having an employees table with one row per employee.
This table would have an auto-generated id specific to your database system. This id would be the primary key of employees and used for all foreign key references.
Any other ids would simply be attributes on the employees table. They are as close to any other table as a single join.
Unfortunately, modifying your existing database for a safer structure would probably be quite a bit of work.