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.

It is true, don't do it.

Even at huge companies like Google, lots of stuff was keyed on your email address. This was a huge problem so Google employees were not allowed to change their email for the longest time. Eventually they opened it up by request but they made it very clear that you would run into problems. So many systems and services would break. Over time I think most external services are pretty robust now, but lots of internal systems still use emails (or the username part of it) and have issues.

IIUC Google accounts now use a random number as the key. But there are still places where the email is in use, slowly being fixed at massive cost.

I'd recommend reading this if you are interested in why making assumptions when designing a system is a bad idea (but often necessary): https://github.com/kdeldycke/awesome-falsehood

Especially the one on names: https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/ And the one on time: http://infiniteundo.com/post/25326999628/falsehoods-programmers-believe-about-time

This is tied to YAGNI. Do you really need to impose this restriction on this database? Don’t add restrictions “just in case it might be useful”. If you don’t have a good reason, you ain’t gonna need it.

I didn't even know that natural keys exist. Who is using them and why?

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