Unique IDs: The Boring Field That Saves Your Migration

Your client tracker has two rows for “Harbour Logistics”. One has a phone number, the other has the billing contact. The invoicing sheet refers to “Harbour Logistics Ltd”, and the project sheet calls them “Harbour”. Are these the same company? Probably. Can you prove it, and can a migration script tell? Not without help.

That help is a unique ID: a boring little code that does more for your migration than any feature you’ll be shown in a demo.

What a unique ID actually is

A unique ID is a value assigned to one record and never to any other. It doesn’t describe the record. It just identifies it, the way a passport number identifies a person without telling you anything about them.

Engineers call the field that holds it a “key”, and when another table refers to it, that reference is a “foreign key”. You don’t need those words, but you’ll hear them, so it’s worth knowing they mean “the ID” and “a pointer to someone else’s ID”.

Good IDs have three properties:

• Unique: no two records share one, ever.
• Stable: once assigned, it never changes, even if everything else about the record does.
• Meaningless: it doesn’t encode facts that might change.

That third one surprises people. Let’s take it seriously.

Why names make terrible keys

Spreadsheets quietly train you to use names as identifiers. You look up a client by name, match rows across tabs by name, and write formulas that search for a name. It works until it doesn’t, and it fails in predictable ways:

• Names aren’t unique. Two employees called Sam Patel. Three projects called “Website refresh”.
• Names change. Companies rebrand, people marry, projects get renamed halfway through.
• Names get typed differently. “Harbour Logistics”, “Harbour Logistics Ltd”, “harbour logistics " with a trailing space. To a lookup, these are three different things.

Every one of these causes a silent break. The lookup returns nothing, or the wrong row, and nobody notices until a report looks odd.

Why “clever” IDs are also a trap

Once people accept they need IDs, there’s a temptation to make them meaningful: LON-ACME-2023-04, for a London client signed in April 2023. It feels tidy. It’s a trap.

The client moves to the Manchester office. Now the ID says London and is wrong forever, or someone changes it, and every reference to the old ID breaks. Meaningful IDs are just names wearing a disguise.

A small prefix to say what kind of record it is (CLI-00412 for a client, PRJ-01187 for a project) is fine and genuinely helpful, because a record’s type doesn’t change. Keep anything beyond that out of the ID and in proper fields where it can be updated.

Assigning IDs to legacy rows

Most proper tools assign IDs automatically to new records. The hard part is your existing spreadsheet rows, which have never had one. Do this before migration, not during it.

Say your team runs a 1,400-row client sheet plus separate project and invoice tabs that refer to clients by name.

1. Deduplicate first. Find the rows that are really the same client and decide which survives. Cleaning Your Spreadsheet Data Before Migration: A Practical Guide covers the techniques. Assigning IDs to duplicates just gives you two IDs for one company.
2. Add an ID column and fill it. A simple sequence works: CLI-00001 to CLI-01400. Fill it once, then convert the column to plain values so the numbers can’t shift if rows get sorted or deleted.
3. Replace name references with IDs. On the project and invoice tabs, add a “Client ID” column next to the client name and fill it by matching. Every row that doesn’t match is a data problem you’ve just found cheaply.
4. Keep the old name column for checking, but treat the ID column as the truth from now on.
5. Freeze it. New clients added to the sheet before cutover get the next number in sequence, assigned by one named person.

Step 3 is where the real value appears. In our example, maybe 60 project rows won’t match any client name. That’s 60 records that would have migrated as orphans, linked to nothing, quietly wrong in your shiny new tool.

Keep the legacy ID after migration

When you import into the new tool, it will probably assign its own IDs. That’s fine. Keep your spreadsheet ID as a separate, read-only field called something like “Legacy ID”.

You’ll be glad you did. During parallel running (see How to Run the Old Sheet and New Tool in Parallel (Without Losing Your Mind)), it’s how you match records between the two. Afterwards, it’s how you answer “what happened to row 873?” when someone finds an old printout. And if an import goes wrong, it lets you re-run it and update existing records instead of creating duplicates.

IDs that humans can use

Internal IDs from tools are often long random strings, perfect for machines and useless for a phone call. Consider a separate, human-friendly reference for records people talk about: REQ-2291 for a request, INV-0457 for an invoice.

Short references make conversations precise. “Can you check REQ-2291?” beats “the one from last Tuesday about the printer, not the other printer one”. They also make search reliable, since the reference is exact and a description isn’t.

Two rules: generate them automatically, never by hand, and never reuse one, even for a deleted record.

Where to start this week

1. Pick your most-referenced spreadsheet, usually clients, people or products, and check whether it has a stable ID column. If not, that’s your first job.
2. Count the name mismatches. Try matching one other tab to it by name and count the rows that fail. That number is your argument for doing this properly.
3. Choose a prefix scheme for each record type and write it down in one line.
4. Decide who assigns new IDs until migration, so the sequence doesn’t fork into two.