Sounds like a good idea. You'll need a 'clean' customer table too of course that contains only the deduplicated customers with the correct id numbers. You'll probably want 4 mapping tables, one for each source database to be sure you map the customer id's to the correct new id's of the deduped customers.
What do you think that is the best way to create a unique Customer Name.
In every database there is a CustomerName, but most of the names are spelled different.
So we have said that our financial system is leading in terms of their names.
It could be that there are prospects in the CRM system but not in the financial system.
What would be the best method that if the customer is in the financial system we want to use that name, otherwise the name of the customer in the CRM system
If the client occurs in system a and system b then they will be linked via the mapping table and is the leading name the name in the financial system.
I hope you can help with this.
Thank you in advance
Well, if you don't have a proper data cleansing tool at hand, you could put all customers from all sources in one table (preferably inclusive address data) and do a load distinct from that to get rid of all the duplicates. Then you'll have to clean up the list. You can use a sound matching algorithm like metaphone to calculate a field from customer names. Similar names will get the same value so you can match on that. And if you have address data for the customers you can use those for matching too. In the end you'll have a list with duplicate candidates. That list you'll have to clean up manually, since you will have to decide if they're really dupes and which one you want to keep and which one should go.