We have three internal web applications we use at my company. Unfortunately none of my predecessors had the foresight of sharing tables amongst those apps.
It has become necessary to share some company data among the three internal web apps. As an example, each of the three websites have a
companies table in their respective databases.
Website A Companies Table
id | name --------- 1 | Sprockleys Sprockets 2 | Goobers Gadgets 3 | Nancys Necklaces
Website B Companies Table
id | name --------- 3 | Sprockleys Sprockets DENVER 22 | Gooburrs Gadgets 30 | Nancyies Necklaces
I want to somehow merge all of this data together into a single shared database table. Unfortunately the ID's of the companies do not match up at all and other tables have foreign keys that map products and users to those companies.
What are some ideas for merging data like this?