Lets say I have a database filled with people with the following data elements:
- PersonID (meaningless surrogate autonumber)
- FirstName
- MiddleInitial
- LastName
- NameSuffix
- DateOfBirth
- AlternateID (like an SSN, Militarty ID, etc)
I get lots of data feeds in from all kinds of formats with every reasonable variation on these pieces of information you could think of. Some examples are:
- FullName, DOB
- FullName, Last 4 SSN
- First, Last, DOB
When this data comes in, I need to write something to match it up. I don't need, or expect, to get more than an 80% match rate. After the automated match, I'll present the uncertain matches on a web page for someone to manually match.
Some of the complexities are:
- Some data matches are better than others and I would like to assign weight to those. For example, if the SSN matches exactly but the name is off because someone goes by their middle name, I would like to assign a much higher confidence value to that match than if the names match exactly but the SSNs are off.
- The name matching has some difficulties. John Doe Jr is the same as John Doe II, but not the same as John Doe Sr. and if I get John Doe and no other information, I need to be sure the system doesn't pick one because there's no way to determine who to pick.
- First name matching is really hard. You have Bob/Robert, John/Jon/Jonathon, Tom/Thomas, etc.
- Just because I have a feed with FullName+DOB doesn't mean the DOB field is filled for every record. I don't want to miss a linkage just because the unmatched DOB kills the matching score. If a field is missing, I want to exclude it from the elements available for matching.
- If someone manually matches, I want their match to affect all future matches. So, if we ever get the same exact data again, there's no reason not to automatically match it up next time.
I've seen that SSIS has fuzzy matching, but we don't use SSIS currently, and I find it pretty cludgy and nearly impossible to version control so it's not my first choice of tool. But if it's the best there is, tell me. Otherwise, are there any (preferably free, preferably .NET or T-SQL based) tools/libraries/utilities/techniques out there that you've used for this type of problem?