how to determine if a record in every source, represents the same person

Question

I have several sources of tables with personal data, like this:

SOURCE 1
ID, FIRST_NAME, LAST_NAME, FIELD1, ...
1, jhon, gates ...

SOURCE 2
ID, FIRST_NAME, LAST_NAME, ANOTHER_FIELD1, ...
1, jon, gate ...

SOURCE 3
ID, FIRST_NAME, LAST_NAME, ANOTHER_FIELD1, ...
2, jhon, ballmer ...

So, assuming that records with ID 1, from sources 1 and 2, are the same person, my problem is how to determine if a record in every source, represents the same person. Additionally, sure not every records exists in all sources. All the names, are written in spanish, mainly.

In this case, the exact matching needs to be relaxed because we assume the data sources has not been rigurously checked against the official bureau of identification of the country. Also we need to assume typos are common, because the nature of the processes to collect the data. What is more, the amount of records is around 2 or 3 millions in every source...

Our team had thought in something like this: first, force exact matching in selected fields like ID NUMBER, and NAMES to know how hard the problem can be. Second, relaxing the matching criteria, and count how much records more can be matched, but is here where the problem arises: how to do to relax the matching criteria without generating too noise neither restricting too much?

What tool can be more effective to handle this?, for example, do you know about some especific extension in some database engine to support this matching? Do you know about clever algorithms like soundex to handle this approximate matching, but for spanish texts?

Any help would be appreciated!

Thanks.

Answer 1

SSIS , try using the Fuzzy Lookup transformation

Answer 2

This sounds like a Customer Data Integration problem. Search on that term and you might find some more information. Also, have a poke around inside The Data Warehousing Institude, and you might find some answers there as well.

Edit: In addition, here's an article that might interest you on spanish phonetic matching.

Answer 3

I've had to do something similar before and what I did was use a double metaphone phonetic search on the names.

Before I compared the names though, I tried to normalize away any name/nickname differences by looking up the name in a nick name table I created. (I populated the table with census data I found online) So people called Bob became Robert, Alex became Alexander, Bill became William, etc.

Edit: Double Metaphone was specifically designed to be better than Soundex and work in languages other than English.

Answer 4

The crux of the problem is to compute one or more measures of distance between each pair of entries and then consider them to be the same when one of the distances is less than a certain acceptable threshold. The key is to setup the analysis and then vary the acceptable distance until you reach what you consider to be the best trade-off between false-positives and false-negatives.

One distance measurement could be phonetic. Another you might consider is the Levenshtein or edit distance between the entires, which would attempt to measure typos.

If you have a reasonable idea of how many persons you should have, then your goal is to find the sweet spot where you are getting about the right number of persons. Make your matching too fuzzy and you'll have too few. Make it to restrictive and you'll have too many.

If you know roughly how many entries a person should have, then you can use that as the metric to see when you are getting close. Or you can divide the number of records into the average number of records for each person and get a rough number of persons that you're shooting for.

If you don't have any numbers to use, then you're left picking out groups of records from your analysis and checking by hand whether they look like the same person or not. So it's guess and check.

I hope that helps.

Answer 5

Just to add some details to solve this issue, I'd found this modules for Postgresql 8.3

• Fuzzy String Match
• Trigrams

Answer 6

You might try to cannonicalise the names by comparing them with a dicionary.
This would allow you to spot some common typos and correct them.

Answer 7

Sounds to me you have a record linkage problem. You can use the references in the link.