We are in the process of rebuilding an entire web application for a client.
Our client's application is an ATS (Applicant Tracking System) SaaS for recruiters., which enables them to post new jobs and manage the applications (plus many other things).
Their current system (code and database) is quite messy so we are writing everything from scratch and building a new database.
As part of the rebuild we need to parse the information from the old database into the new database. This is going to be quite difficult due to the poor database design in their current system.
We have identified a problem for the data migration plan.
In the current system, Applicants have 2 unique forms of identification. For example John Doe has a user_id of 4 but a applicant_id of 9.
We have been able to identify sections of the site where user_id is being used instead of applicant_id (for example the applicant's experience and education) which will lead to errors when we begin the data migration.
This is a serious issue as we will be using these ids to attribute data to Applicants and Companies. If we are not extremely careful in checking all instances where the ids could be mixed up, we could potentially associate job applications/messages/experiences/skills etc to the wrong Applicant or Company which would render the data useless and worse potentially expose sensitive company information.
Can anyone offer any advise or expertise on the above? Thank you in advance for your help.