How would one merge two databases that use different keys? [closed]

Question

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?

Answer 1

There's not one specific right answer to your question but the thought process you went through in it is the correct way to try to solve the problem.

In lieu of a randomly generated unique primary key, sometimes tables have something known as the natural key. If the natural key is composed of multiple columns, it is known as a composite key as well. An example of this is a UserAccounts table that has a UserId primary key, but also has a LoginEmail column with a unique constraint on it, making it a natural key.

The best thing you can really do is come up with an algorithm, which generally can be as simple as picking a set of fields that make the best composite natural key candidate and joining the two tables between the two databases on those fields.

Then you have 3.5 cases you have to handle:

1: When there is a truly distinct match of one and only one record as a result of the join. In this case you can be fairly confident it's correct and no need for further processing.

2: When there are multiple rows that match between the two tables. Usually human intervention is needed if no other process exists to decide how to proceed. E.g. if there was a CreatedDate for the records, you could throw away everything but the most latest CreatedDate records (if it made business sense). Human consolidation is not uncommon for this case.

3.5: When no records match in Table1 for Table2 (and visa-versa, there's records in Table1 that don't find a match in Table2). If that's the case then you basically need no further action since there's only one source of truth.