These days I follow a hard heuristic: Always use synthetic keys for database tables.

And the way to follow this rule is fairly simple, but it has a few twists.

For internal use, the best and most common key (in a relational database) is an auto-generated incremental sequence. But it it ok to use it externally? – across databases, across types of data storage, across APIs / services etc.

It’s tempting to refer to the sequence number in API calls, after all they are going to that particular database and are only going to be used with it, right? Well not necessarily; the database and the code powering the API are different systems, who says there won’t be other apps accessing the database for example.

The current OpSec school of thought is that sequence keys are an internal database mechanism and sequence numbers should only be used for internal consistency, never used as external references (even for the “local” API).

Sequence keys also don’t offer any way to deal with creating duplicate data entries. If you’ve been around for a while you’ve seen this, the client sends the same “create” request twice for whatever reason (UI lets user multiple-click a button, client assumes timeout when in fact it had gone through etc.) Some programmers attempt to run heuristics on the data and ignore successive create attempts that look “too similar” but it can backfire in many ways.

An UUID is pretty much universally supported nowadays, its designed to be unique across a vast amount of systems, doesn’t give anything away about your internal mechanisms, and if you ask the client to generate the UUID for create requests you can neatly solve the duplicate issue.

Do keep in mind that this doesn’t solve the problem of bijection across many years and many systems and many databases. An entity may still acquire multiple UUID’s, even if they’re each individually perfectly fine.

There can also be circumstances where you have to offer people a natural-looking key for general consumption. You can’t put UUID’s on car plates for example.

Got hands on experience with this. Wasn’t my design choice but I inherited an app with a database where one of the keys was tied to a completely separate database. I mean at the time it probably made sense but the most unlikely of scenarios actually happened: that other database, the one I had zero control over, was migrated to a new platform. All of those keys were synthetic so of course they were like, “Meh, why we gotta keep the old keys?” So post-migration my app becomes basically useless and I spent 6 hours writing migration code, some of it on off hours, to fix my data.

So it’s questionable whether a foreign key of a completely different system is a natural key, but at the very least never use a key YOU don’t control.

A well argued point. Could have done without the random transphobic comments about “transsexuals” and “perceived gender”.