Why every table needs a stable ID (and why it shouldn't be a name)

Ask most people how they identify a customer in their spreadsheet and the answer is "by their name". It works right up until it does not, and when it fails it fails quietly.

Why names are bad identifiers

Names are not unique. Two James Smiths. Two companies called Apex Consulting in different countries. Two products called "Blue widget" from different suppliers.

Names change. People marry. Companies rebrand or get acquired. Products get renamed between generations. If the name is the identity, a rename is indistinguishable from a different thing.

Names get mistyped. Acme Ltd, Acme Ltd with a trailing space, ACME Limited. To you, one company. To any system matching on text, three.

Names get formatted differently. "Smith, Jane" and "Jane Smith". +44 20 7946 0958 and 02079460958.

Every one of these silently splits one real thing into several, or merges several into one. Both are corruption, and neither raises an error.

What a stable ID is

A stable ID is a value whose only job is to identify a record. It has three properties:

1. Unique — no two records share it.
2. Permanent — it never changes, even when everything else about the record does.
3. Meaningless — it carries no information, so nothing about the world can make it wrong.

That third one surprises people. Meaninglessness is a feature. An ID that encodes something — a region code, a year, the customer's initials — becomes wrong the moment the thing it encodes changes. LON-2024-SMITH is a liability the moment Smith moves to Manchester, changes their name, or you realise you need a second record for them.

Most tools give you this automatically: an internal record ID assigned on creation that you never have to think about. When you link two tables, that is what is actually stored — which is exactly why links do not break when you rename things.

Where you still need to think about it

Automatic IDs handle links. Two situations still need a decision from you.

1. A human-readable reference

Internal IDs are usually long and ugly. When a person needs to quote a reference — an invoice number, a ticket number, an order number — you want something short and sequential.

That is an auto-number field: INV-1043, TICK-0291. It is assigned once, never reused, never edited.

Two rules:

• Never reuse a number, even after a deletion. If invoice 1043 is voided, 1043 stays voided. Reusing it means two different documents share a reference, which is an audit problem.
• Never renumber. Sorting or bulk-editing should never change existing numbers. If your "ID" changes when you sort, it is not an ID.

2. Matching against the outside world

When importing, or syncing with another system, you need to decide what counts as "the same record". This is where most data quality is won or lost.

The order of preference:

1. A shared identifier both systems agree on. Your customer number in their export. Ideal, when it exists.
2. A naturally unique attribute. Email for people, SKU for products, VAT number for companies. Good, with caveats — people change email, and share family addresses.
3. A composite. Name + postcode. Workable, fragile.
4. Name alone. Avoid. This is the failure described at the top of this article.

If you are importing repeatedly from the same source, store their identifier in a dedicated field — Source system ID — the first time. Every subsequent import matches on it and cannot get confused by a rename.

The duplicate problem

Even with good IDs, duplicates arrive — usually through imports, forms or two people creating the same record on the same morning.

Prevention beats cleanup:

• Mark the natural key field unique where the tool allows it. An email column that refuses a second copy stops the problem at the door.
• Prefer picking over typing. If a form makes people select an existing client rather than type a name, duplicates mostly stop happening. This is the single highest-leverage change.
• Import into a staging view and review before merging.

When cleaning up, merge rather than delete. A duplicate usually has links pointing at it — orders, tasks, notes. Deleting it orphans them. The correct sequence is:

1. Decide which record survives.
2. Repoint every link from the duplicate to the survivor.
3. Copy across any field the duplicate has and the survivor lacks.
4. Only then remove the duplicate — ideally by archiving, not hard deletion, so the change is reversible.

A worked failure

A supplier list keyed by name. Over two years:

• "Northwind Traders" is entered by one person; "Northwind Trading Ltd" by another. Two suppliers, both real, half the purchase history each.
• Northwind is acquired and renamed Vela Supply. Someone edits the name on one record. Purchase orders referencing the old name by text now point at nothing.
• A report on "spend by supplier" shows three suppliers where there is one, none of them with the right total.
• Nobody notices for a quarter, because every number is plausible.

The cost is not the cleanup. It is the quarter of decisions made on wrong numbers.

With stable IDs and relation fields, none of it happens. The rename is one edit to one record; every link follows automatically because the links never pointed at the name.

The five-minute checklist

For each table:

• Is there something that uniquely identifies a record and will never change? If it is the name, that is a problem.
• If people quote a reference out loud, is there an auto-number for it?
• If data arrives from another system, is their ID stored in its own field?
• Are links built on relation fields rather than typed text?
• If the most-renamed thing in this table were renamed tomorrow, what would break?

That last question is the fastest audit there is. If the answer is "several reports and some links", fix the identity before you build anything else on top.

Where to go next

• Linked records explained — why real links survive renames.
• Normalization for non-engineers — the one-fact-one-place principle.