Data migration to a single shared SQL table

Question

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?

Answer 1

Make sure everyone understands how difficult this will be for everyone. Identify the benefits to the business. What sounds good in theory doesn't always work in practice.

Everyone involved except the developers are going to want some perfect solution that will automatically merge all of these different company ID's (get rid of the duplicates) without any manual data entry on their part. Because they are so certain things like names, addresses and phone numbers will be a perfect match. Don't believe it until you can prove it.

Another is when you're working on cleaning up this list, new duplicates are going to get entered.

1. Records from all 3 tables get copied to the new table so: A) They get a new primary key B)Keep track of the old ID with duplicates in a column C) have a column to identify which database they originated in.
2. Propogate the new key to all three databases and update foreign key records (The Old ID and database identifiers make this possible.
3. Insert code to use new single table.
4. Update constraints to use new table.

You could do this in stages where you have all the customer CRUD use the new table and a trigger of some sort to keep the 3 existing tables in sync. This depends on how long it takes you have to change the code to use the new table for all the other functionality. There may be reporting tools with hardcoded connections. Basically your running the single-table system in parallel with the triple-table system.

You'll also need a feature in your application to merge duplicate company entries. Automate this as much as you feel comfortable, but eventually there needs to be a manual process. There is nothing to prevent a user from entering the same company twice. You can restrict and constrain and check for duplicates and give all the error messages you can think of and someone is going to do whatever it takes to jam that company in there. Someone will spot it and ask you how are we going to fix this.

Answer 2

You need to do this in three stages.

1. Create a new central table with a new key, the name address details etc. and a column for the key values the existing tables. Load up the data from the three sources into here.

2. Identify and merge duplicate rows. This has to be a hybrid automatic/manual procedure. Have a program identify possible duplicates then have a manual approve/reject process.

3. Switch the three existing apps to use the new central DB. BUT each app needs to use its "old" key to access the data (database views can help here)!

There are still some issues to be resolved -- you will need a common app to search for existing customer and/or add new customers, -- you will need a (mostly manual) process for resolving different entries for the same customer ("Acme Widget Company" v. "Acme Widget Co." etc.).

Answer 3

Since the complexity is better be served by small portions, I think this can be done with two big steps:

• Create an intermediate table [CompanyName],[OldId1][OldId2]...[NewId] (if the company name is what they share). Don't know what your DBMS is, but for example Sqlite allows attaching databases in the same session and working with them on common queries. This step is safe for your data and apart from creating it, I'd look at different selects and joins of this new table with separated tables since the human eye is the best tester if something is wrong with your approach. Even simple select from this table will allow you to catch such anomalies as unexpected NULLs.
• Perform necessary modifications to the existing and new table based on this new mapping.

Answer 4

Use the propositions above to merge the 3 tables into one. Make sure you keep in the new table the old key.

After you do that the major problem is to consolidate the same company that has different ids.

It depends how much time and resources you have.

1) Solution "i have time and resources"

If you have enough the best thing you can do is export the tables in excel and put people work on it in order to identify the same companies in the 3 tables. Do it simple, put people that know excel with people that now the companies.

2) Solution "i dont have time know"

If you need to start operating asap then you will have to:

• Just merge the tables with a new key and keep the old one.
• Use a mechanism like views or something in order for the apps to work immediately as before (with the old key).
• Create a simple lookup table (common company code) where gradually you will consolidate the companies.

More on the last point:

Create common reports for all the companies at the very beginning . The results from these reports will have the same company more than one time BUT they will outline to the management the need to commit resources in consolidation.

I hope i made it more clear for you....