The same entity in two systems
One person or organization exists in two systems under different identifiers, with nothing linking the records.
What it looks like
Before
| system | local_id | name | shared_id | |
|---|---|---|---|---|
| billing | B-7781 | Priya Nair | priya.nair@example.com | · |
| support | S-0432 | P. Nair | priya.nair@example.com | · |
| billing | B-9020 | Sam Ortiz | sam.ortiz@example.com | · |
| support | S-1188 | Samuel Ortiz | sortiz@example.com | · |
After
| system | local_id | name | shared_id | |
|---|---|---|---|---|
| billing | B-7781 | Priya Nair | priya.nair@example.com | P-001 |
| support | S-0432 | P. Nair | priya.nair@example.com | P-001 |
| billing | B-9020 | Sam Ortiz | sam.ortiz@example.com | needs a decision |
| support | S-1188 | Samuel Ortiz | sortiz@example.com | needs a decision |
How it happens
- Each system issues its own identifiers and was never designed to know about the other.
- Organizations merge, or a team adopts a new tool, and the old records are not linked to the new ones.
- The fields the systems have in common, such as name and email, are entered separately in each and drift apart.
What it breaks
- No report can show one customer's full history, because the parts cannot be joined.
- Counting across systems double counts everyone who appears in both.
- A change made in one system, such as a new address or a request to stop contact, never reaches the other.
How to spot it
- Look for a field both systems hold, such as email, phone, or a tax ID, and check how many values overlap.
- Compare the count of entities in each system with the count in the combined list. If the combined count equals the sum of the two, nothing was linked.
- Sample a few well-known entities and search for them by hand in both systems.
How to fix it
- Standardize the shared fields in both systems first. Matching on unformatted values misses most pairs.
- Link records with a mapping table that pairs the two identifiers, and leave each system's own identifier in place.
- Decide what counts as enough evidence for a link. A shared email is strong; a similar name alone is not.
- Send uncertain pairs to a person. A wrong link merges two people's histories, and that is harder to find later than a missed link.
- Near-duplicate recordsTwo rows describe the same real thing but differ enough that an exact comparison misses them.
- Name variantsThe same person or organization appears under several spellings, orders, and abbreviations.
- Duplicate keys with conflicting valuesTwo rows share an identifier that should be unique, but disagree on other fields.
- Case example: UtilitiesEquipment records that could not be tied to what was spent on them
- Case example: E-commerceA catalog where the same product was listed four times
TermsEntity resolution, Record linkage, Crosswalk, Golden record