Normalizing Accented Names for Database Matching

You have a customer table with José García, Jose Garcia and JOSÉ GARCIA in it, and they are one person who signed up three times. Folding the accents merges them correctly. Applied to the whole table, the same fold also merges Peña with Pena, and those are two different families.

What the fold is for

Name deduplication is not the same problem as diacritic-insensitive search, even though both start with the same normalisation. A search that folds too aggressively returns some results the reader did not want, and they scroll past. A merge that folds too aggressively destroys a record: two customers become one, their orders combine, their addresses conflict, and there is no undo unless you kept the originals. The asymmetry means the fold has to be treated as a candidate generator rather than as an answer.

So the design is: fold to find pairs worth looking at, then require a second, independent signal before anything is merged. The fold does recall. Something else does precision.

The fold, in SQL and in code

The normalisation itself is the standard decomposition-and-strip, plus the handful of letters that have no canonical decomposition, plus whitespace and punctuation flattening, because names arrive with non-breaking spaces, curly apostrophes and inconsistent hyphens.

export function nameKey(raw) {
  return raw
    .normalize("NFKD")
    .replace(/\p{Mn}/gu, "")        // strip combining marks
    .replace(/\u00DF/gu, "ss")      // ß  — no decomposition
    .replace(/\u00F8/gu, "o")       // ø
    .replace(/\u0111/gu, "d")       // đ
    .replace(/\u0142/gu, "l")       // ł
    .replace(/[\u2018\u2019\u02BC']/gu, "")  // O'Brien, O’Brien, Oʼ…
    .replace(/[\s\u00A0\u2010-\u2015-]+/gu, " ")
    .trim()
    .toLowerCase();
}

nameKey("José García")   // "jose garcia"
nameKey("JOSÉ  GARCIA")  // "jose garcia"
nameKey("O’Brien")       // "obrien"
nameKey("Müller")        // "muller"      — note: not "mueller"

On the database side, PostgreSQL has unaccent, and there is a trap in using it that costs an afternoon. unaccent() is declared STABLE rather than IMMUTABLE, because its behaviour depends on a dictionary that can be changed. An expression index requires an immutable expression, so CREATE INDEX ... ON people (lower(unaccent(surname))) fails with functions in index expression must be marked IMMUTABLE. The usual workaround is an immutable wrapper, and it is a promise you are making on the dictionary’s behalf: if the dictionary changes, the index is silently wrong until it is rebuilt.

CREATE EXTENSION IF NOT EXISTS unaccent;

-- The wrapper is what makes the index legal. It is also a promise
-- that the unaccent dictionary will not change under it.
CREATE FUNCTION immutable_unaccent(text) RETURNS text
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
  AS $$ SELECT public.unaccent('public.unaccent', $1) $$;

ALTER TABLE people
  ADD COLUMN surname_key text
  GENERATED ALWAYS AS (lower(immutable_unaccent(surname))) STORED;

CREATE INDEX people_surname_key_idx ON people (surname_key);

A generated column is preferable to an expression index here because the key is a thing you will want to read, group by and export, not only search on. It also makes the fold’s version visible: when you change the definition, the column is rewritten, and every consumer gets the new value at once.

Where the fold merges two people

Here is a labelled list, of the kind you would get from a real customer table, with the key each name produces.

surname     key       note
--------    -------   ------------------------------------------------
García      garcia    same family as Garcia below
Garcia      garcia    ✓ correct merge — accent dropped at signup

Peña        pena      Spanish, "rock"
Pena        pena      ✗ WRONG — Pena is a separate Spanish/Portuguese name

Şen         sen       Turkish, "cheerful"
Sen         sen       ✗ WRONG — Sen is a Bengali surname

Bąk         bak       Polish, "horsefly"
Bak         bak       ✗ WRONG — Bak is a separate Polish/Danish name

Müller      muller    German
Mueller     mueller   ✗ MISSED — the ue transcription is not folded to ü

Solé        sole      Catalan
Sole        sole      ✗ WRONG — Sole is an Italian surname

Two failure directions, and they need different answers. The false merges happen because in Spanish, Turkish and Polish the diacritic is a letter distinction rather than an accent on a letter: ñ is the fifteenth letter of the Spanish alphabet, ş and ç are letters of the Turkish alphabet, and ą is a letter of the Polish one. Folding them is not removing decoration; it is deleting information the language treats as lexical, the same mechanism that makes Polish sorting go wrong.

The Müller/Mueller miss is the opposite: two forms that are the same name and do not fold together, because the German umlaut transcription rule (ä→ae, ö→oe, ü→ue, ß→ss) is orthographic convention, not normalisation. Adding it to the fold as a second candidate key is reasonable; adding it as the only key is not, because it also merges Bauer-style names that legitimately contain ue.

A blocking key, not an identity

The way out is to stop asking the key to decide anything. In record linkage the folded value is a blocking key: its job is to reduce a quadratic comparison to a manageable number of candidate pairs, and nothing more. Two records sharing a key are compared properly; two records not sharing one are never compared.

What counts as a proper comparison is a product question, but the shape is consistent. Require at least one strong identifier to agree — an email address, a phone number, a national identifier, a date of birth plus postcode — before merging automatically. Where only the name agrees, produce a review queue rather than a merge. And where the two names differ in their original form even though their keys agree, weight that as evidence against a merge rather than as neutral: Peña and Pena arriving as typed are more likely to be two people than José and Jose, because dropping an accent is a common data-entry accident and adding one is not.

The deduplication pass

1. Add the key as a stored generated column and index it. Do not compute it in the query; a function call on every row of a large table turns a candidate-pair search into a sequential scan.
2. Group by the key and count. Look at the distribution before you look at the pairs: a key with forty records is usually a common name in one language, not forty duplicates, and treating it as a merge candidate is how a deduplication run destroys a table.
3. Join candidates on the key and score each pair on the independent signals you actually have. Emit a score, not a boolean.
4. Split by score into auto-merge, review and ignore. Set the auto-merge threshold so that it requires a strong identifier match; name agreement alone should never reach it.
5. Sample the review bucket by language before running anything. If the sample contains Spanish, Turkish, Polish or Vietnamese names, expect the false merges above and check them by hand.
6. Write the merge as an insert into a merges table plus a pointer, not as a delete. The unmerge path is what makes the whole pass safe to run again.