Moving From Old System To New System

Hi,

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.
oo7mlAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul MacDonaldDirector, Information SystemsCommented:
"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."
Is this not happening already?

Is there a reason a User_ID would need more than one Applicant_ID?  I can see where that would be useful.

Are there User_IDs with more than one associated Applicant_ID?  This doesn't answer the previous question, but it does change your strategy.

If there's not a need for multiple Applicant_IDs, and no one has multiple Applicant_IDs, I would eliminate them in the new system, simply converting Applicant_IDs to User_IDs in the database structure and in code.

My $.02
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you have identified all userid vs applicantid, then elect the one that you want to keep and do not import the other field.
For that add the field that you want to keep in the target table before you migrate the database. Example:
- If you want to keep the userid, add that column in the applicant table and for the already identified cases, provide the correct userid value and then create a relationship with the user table on the same column.
0
nociSoftware EngineerCommented:
To prevent loss, you could create for every entry with a userid/applicantid:
when copying this userapplicant entry to the new table then add BOTH userid referenced items to their tables while creating a new number in the attribute table,   and ALSO the applicant attributes to that same new number.....   That should prevent misattributions....  AND should also remove doubles.  
(effectively you are renumbering all attributes to a new unique "userid/applicantid" combo.

(that is assuming the source is somehow  not contaminated already).
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Recommendation to close this question by accepting the above comment as solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.