Duplicate Records: Detect and Resolve, or Prevent

Duplicates happen. For lots of reasons. Some legitimate, some accidental. Duplicate records can cause all sorts of problems. Examples of these are:

• Incorrect reports, totals, counts, and dashboards
• Repeated invoicing and double payments
• Multiple contacts for the same thing (email, SMS, etc) with the same person
• Conflicting data across duplicates (email, phone, notes)
• Poor customer experience leading to reduced trust in the database and the business

How can this happen?

The most obvious cause is when someone creates a new record without first checking whether that person, company, or other entity already exists.

Maybe you did do the right thing and checked for an existing record. But you searched for Bob Johnson and found no records. In fact, there was a record for this person but it was for Robert Johnson. Similarly, a search for Landscape Masters Pty Ltd may miss an existing company entered as Landscape Masters.

Duplicates can also be introduced through a poorly configured import or integration. When data arrives from a source file, API, or another system, it needs to be compared to existing records before new records are created.

Duplicate records can easily be caused by a slip of a keyboard shortcut. There is more than one time that I have meant to enter Find Mode (Command-F) and accidentally hit Command-D. In Form View it is not obvious that a duplicate has just been created. The user may think "huh?" and continue with their find.

Fix or stop?

There are a couple of ways to deal with this problem:

1. Detect and resolve
2. Prevention

In practice, the most effective solution usually combines both approaches.