Unique identifiers and why duplicates quietly break your books

Here’s a piece of database thinking most accountants never get taught but that explains a surprising number of bookkeeping headaches: every record needs a unique identifier, and when your system relies on the wrong one, duplicates creep in and quietly corrupt your numbers.

What a unique identifier is

Each master record — a customer, vendor, account — has something that identifies it uniquely and stably: a customer ID, an account number. That identifier is how transactions reliably point to the right record. The customer’s name is a label for humans; the ID is what the data hangs on.

Why matching on name is fragile

Trouble starts when data is matched or merged by name instead of a stable key — exactly what happens in many imports and manual entries. “ABC Ltd,” “ABC Limited,” “ABC Ltd ” (trailing space) and “abc ltd” are one company to a human and four different strings to a computer.

What duplicates do to your numbers

A duplicated customer splits one company’s activity across two records — each shows half the balance, the aging report misleads, statements go out wrong. A duplicated vendor understates what you owe and can understate their 1099. Nothing errors out; the totals just quietly stop reflecting reality. Duplicates are dangerous because they’re silent.

Why it matters most in a migration

Moving data between systems is where keys break down, because the source’s identifiers carry no meaning in the destination. Good conversion doesn’t just copy rows — it de-duplicates against a shared key first, and when data is split across files, merges them on a reliable key like invoice number, handling the case-and-format mismatches that trip a naive match.