I have Database 1, and Database 2. Database 1 is the companies native data on clients and Database 2 is an external database being ported over from a different company. I'm trying to find what customers (Database 1) in our database, exist in their database (Database 2).
Database 1 has: customer_id (PK), customer_name, customer_email, customer_phone, customer_location, etc...
Database 2 has: customer_key (PK), customer_name, customer_email, customer_phone, customer_location, etc...
The question is: Find a way to link Database 1 to Database 2 so that each person in Database 1's information is joined with their respective information in Database 2.
The problem is: these databases have two different primary keys (or ids assigned to an actual real life person). So you can't just simple query them based on the PK "customer_id" because "customer_id" does not exist in Database2 and vice versa.
Initially I think of using a variable that is unique (i.e email) and matching on that...
But... what if the email they provided in one of the databases is "shared", meaning, in Database 1, that email is actually just an email the business shares for a bunch of different departments... Then it's not really helpful in linking back to a single person. Or if in Database 2 they provided a shared email, so many different records if grouped together on email will show they have multiple names, and phone numbers associated with them... This gets even more complicated when all the variables in the database (phone, name etc...) also have this "shared" problem for some of their entires.
People can also change companies (changes their email and possibly phone) or how people change their first and last name (more so last name).
The only solution I can think of at the moment is to use some sort of combination of verifying the email matches, then verifying that phone, name and location do as well. The more items that they match on between databases, the more certain one can be that "customer a" in Database 1 is "customer 123" in Database 2.
How do you guys deal with this? Do you use a LIKE condition or something similar and just set a threshold for similarity when joining?