Duplicate records and the customer who exists twice

The duplicate records problem is two or more rows in your database or CRM that describe the same real customer. It happens because intake channels check for exact matches while people type details differently — a capitalised email here, a space there. The fix is exact-key constraints plus reviewed fuzzy matching, never blind auto-merge.

Key Takeaways

The duplicate records problem is a data-model gap before it is a cleanup task. People type their details differently across channels, so exact-match checks miss real customers. Fixing it means finding pairs, merging safely inside a transaction, then adding a database constraint so the same twin cannot be born twice.

• Two rows about one person is a data-model failure, not a staff discipline problem.
• Duplicates split spend, history and consent across rows, so reports and deletion requests both fail.
• Find pairs in order: exact matches, then normalised keys, then reviewed fuzzy matches.
• Merge inside a transaction; archive the twin; keep a backup and an audit trail.
• A unique index on the normalised key is the only guard nobody can forget.
• Auto-merge on name similarity is riskier than the problem it solves.

What is the duplicate records problem?

A duplicate record is a second row in your database or CRM about a person the system already knows. The rows disagree on small details — a capitalised email, a missing digit, a maiden name — so no exact key catches the twin. One customer, two identities, split history.

Engineers call the agreeing detail a natural key: the one value only one person can own, usually an email address or a phone number. When two rows share a normalised natural key, they are the same customer. When the system never checks, the customer who exists twice is born.

Why is one customer who exists twice expensive?

Duplicates cost money quietly. They split one customer's lifetime value across two rows, send the same campaign twice, and break deletion requests because the privacy tooling only removes the row it matched. Support suffers too: the ticket lives on one record while the billing dispute sits on its twin.

The damage compounds without ever throwing an error. Marketing measures two half-customers instead of one real one. An automation re-books the wrong row. A support agent apologises for an email the customer already answered — on the other record. That silence is what makes it expensive.

How do duplicate records get created?

Duplicates form wherever two intake paths write to the same table without a shared natural key. A website form, a phone order and a CSV import each insert independently, and exact-match checks pass because the strings differ. One double-clicked submit button can create two rows on its own.

• Several intake paths — website form, front desk, point of sale, import — with no shared rule for what makes a person.
• Double-submits: no idempotency key, so a retry inserts twice.
• Exact-match-only checks: [email protected] and [email protected] look different to the query.
• Formatting: a trailing space or a differently written phone number defeats equality.
• Renewal: the customer returns years later with a new email, and nothing links the rows.

The common thread: nobody ever told the database which detail makes two rows the same person.

How do you find duplicates without merging the wrong two people?

Start with exact matches on a normalised key, then widen the net slowly. Lower-case and trim the email, strip formatting from phone numbers, and only then run fuzzy name matching. Review every fuzzy pair by hand, because similarity alone cannot tell two different people apart.

1. Take a backup first. Everything after this step ends in a merge, and a merge is not undone with Ctrl+Z. Use pg_dump for PostgreSQL or mysqldump for MySQL.
2. Count exact twins on the raw column:

SELECT lower(trim(email)) AS email_key, count(*) AS twins
FROM customers
GROUP BY lower(trim(email))
HAVING count(*) > 1
ORDER BY twins DESC;

3. Normalise and recount. Trimming and lower-casing surfaces the twins hiding behind capitalisation and stray spaces.
4. Pair on phone. Store a digits-only copy — no spaces, dashes or country code — and match on that.
5. Run one fuzzy pass. In PostgreSQL, CREATE EXTENSION pg_trgm; then join candidates on matching phone digits and score name similarity above roughly 0.5. The pg_trgm documentation covers the functions.
6. Review by hand and note each pair's source column. The channel that spawns the most twins is your leak — usually the phone desk.

Require two signals before you believe a match. Gurung, Sharma and Tamang repeat constantly, so a name match alone proves nothing; ask the phone number or address to agree as well.

How do you merge a duplicate safely?

Merge inside a database transaction, with a backup taken beforehand. Re-point the child rows — orders, tickets, notes — to the survivor record, mark the twin archived rather than deleted, and log which staff member approved it. If any step fails, the transaction rolls back and both records stay intact.

Pick the survivor deliberately: usually the row that carries the billing history or the older consent record. Then re-point everything that references the twin:

BEGIN;
UPDATE orders    SET customer_id = 4102 WHERE customer_id = 5877;
UPDATE tickets   SET customer_id = 4102 WHERE customer_id = 5877;
UPDATE customers SET archived_at = now() WHERE id = 5877;
COMMIT;

Before you run this: it changes live rows. Take a backup, rehearse on a restored copy first, and never DELETE the twin — an archived row is your audit trail and your undo.

How do you stop the next duplicate?

Prevention lives at the database layer: a unique index on the normalised key makes the second insert fail outright. Add idempotency to public endpoints so a double-submitted form inserts once, route every intake through one canonical create path, and run a weekly fuzzy report that a human reviews.

In PostgreSQL a functional unique index does it in one line:

CREATE UNIQUE INDEX customers_email_key
ON customers (lower(trim(email)));

The index refuses to build while existing rows break the rule, so clean first, create second — and schedule the build off-peak, because indexing takes a lock on busy tables. MySQL's default collations already compare case-insensitively, so an ordinary unique index catches the capitalisation twin there. The full rules sit in PostgreSQL's unique-constraint documentation. Then close the retry hole: an idempotency key is a token the client sends so repeating a request returns the first result instead of inserting again. Disabling the button after click helps, but two browser tabs defeat it — the server has to decide. If nobody owns this, our team can fold a monthly duplicate report into ongoing website maintenance.

What does a cleanup cost in time and risk?

A cleanup costs engineer and staff time rather than licence fees: extracting pairs, reviewing them, merging in batches. The real risk is the false merge — stitching two genuine customers into one is worse than the duplicate you started with. That is why auto-merge rules need a human threshold.

A small list needs none of this machinery: one person, a spreadsheet, an afternoon. What a proper review involves is a common question — our FAQs answer the usual scoping ones.

Do duplicates create a privacy and security exposure?

Duplicates quietly break privacy requests. A deletion run that removes one row leaves its twin behind, still holding the person's phone number and purchase history, so the request fails without anyone noticing. Every extra copy of personal data also widens what you must protect and audit.

What mistakes keep the duplicate records problem alive?

The classic mistake is cleaning up without adding a constraint — the same twins return within weeks. Others follow: trusting email uniqueness when it can be blank or shared, auto-merging on name similarity, and hard-deleting the loser so no audit trail survives. Each one costs a second cleanup.

• Blocking signups on fuzzy suspicion — you punish real customers for a guess.
• Matching on email when a household shares one address; sometimes phone is the honest key.
• Merging in production without rehearsing on a restored backup copy.
• Assuming the front desk will "just be careful" — the database must decide, not memory.

What does the duplicate records problem look like in practice?

Picture a Kathmandu trekking agency. A customer books the Annapurna circuit through the website in January, then phones the office in February and books again. The staff member types a fresh record with a different email spelling, and both bookings sit on separate rows about the same man.

He gets the same festival-discount email twice and asks to be removed — from an address that still lives in the other row. His deposit sits on one record, his balance on the other, so accounts chase a payment he already made. Sum lifetime value by customer and the agency's best repeat trekker looks like two first-timers. The repair took an afternoon; the prevention took an hour. When we build booking systems and portals, intake runs through one canonical path — see our custom software development and a few builds on our portfolio.

Which approach fits your data?

Match the guard to the volume. A unique constraint costs minutes and belongs on every table with a natural key; fuzzy reports suit mid-sized lists; purpose-built merge tooling pays off only where staff create records daily. Whatever your existing system already ships, use that before building anything.

Approach	Best for	Watch out for
Unique constraint on the normalised key	Every table with a natural key, from day one	Agree the normalisation rule first — case, spaces, country codes
Weekly fuzzy report, human-reviewed	Lists of thousands of rows	Needs staff minutes every week, indefinitely
Merge action inside your admin UI	Portals where staff create records daily	Build effort up front; permissions must limit who merges
Your existing system's dedup tooling	Teams already running a CRM that ships one	Its matching rules are the vendor's — check them
One-person manual cleanup	A few hundred rows	Returns within weeks unless entry is fixed too

Blast radius — how much of the system a bad change can hurt — is the deciding test. A wrong merge touches invoices, tickets and consent at once, so wherever pairs stay ambiguous, keep a human in the loop.

In short

The duplicate records problem starts at intake and ends at the database. Normalise the key, count the existing twins, merge them inside transactions with a backup, and add the unique index that makes the next one impossible. Keep a human reviewing fuzzy matches, and the second identity disappears.

People also search for

These related guides answer the questions that follow a duplicate records problem: where the data actually lives, who can see it, how to honour a deletion request, and when a portal needs building at all. Each is written for the person who owns the system, not the vendor.

• Where is customer data actually stored?
• How do I handle a customer data deletion request?
• Are we collecting customer data we never use?
• Who should be able to see patient records?
• Does duplicate content on a website hurt SEO?
• When is a customer portal worth building?

If duplicates have outgrown a spreadsheet review, tell us what you are seeing — we will look at the intake paths and the schema and hand you a written plan you keep either way. Whether it ends in a small cleanup or a rebuilt customer system, the first step is the same honest review.