Is there ever a reasonable case for a "natural ID" like we were taught in database school? In my working experience, I always use either an autoincrementing integer or a random string / uuid as the primary key.
Sure, so long as that ID actually identifies the thing in question. Email addresses ARE a great natural ID for an e-mail mailbox. If you want to store a history of what e-mails a particular address has received, they're great. They are not a good identifier for a user since they don't describe the same thing, just two things that happen to coincide right now.
An email address is a great natural ID for an email address, but an address isn't a mailbox. Different addresses can go to the same mailbox, and the same address can go to different mailboxes based on filtering.
A few examples come to mind: geographic regions that already have codes or abbreviations associated with them (e.g. a FIPS code or state abbreviation in the US), simple enums (though I really only bother putting these in a table if they’re used in multiple places). Have to be careful using a string as the clustered index of a large table, though. Smaller tables with shorter string keys that experience very few writes are most ideal for these cases.
The only natural key a human being will normally be able to follow is their DNA, and I still think chimera-ism or tissue donation or other genetic anomalies might make even that inconsistent.
Most natural keys that identify dimensions of your data are slowly changing anyway - an address isn't a permanent fixture of a building, an area code splits, nations go to war, daylight savings changes on a whim, laws change, rules change, our understanding of the universe changes.
1. Built-in Deduplication. If your ID describes the "thing that it is" handling deduplication is much easier. Of course you can try to enforce it by enforcing uniqueness on other columns (but that's not always possible / can get tricky).
2. Save a DB trip on updates/some creations / make indexing more explicit. E.g. say you are user with email address and you want to update some info. Either you run an update using the email directly (in which case you are treating it as a PK essentially even if you don't call it that) or you first retrieve the relevant PK by whatever logic (say indexing by email and name or whatever) at which point you do have an implicit natural key.
In my experience every time I have seen a "corporate" DB with non natural key you get a LOT of duplication and all sorts of services to try to resolve entities running in a batch way (the horror..).
And 2 leads to a lot of bugs because of the implicit nature where you accidentally update the wrong or multiple rows because you didn't know what the implicit uniqueness "key" entailed.
1) might degrate into overzealous real life deduplication. I know a guy who meets with his peers (having the same name and birth date) to exchange letters once a year.
2) Usually, the update does not happen in the blue. It's often done after a load operation, for optimistic locking. In that case, the PK is available for free.
One notable problem with user ids is that they tend to leak to users in some way. Because of this, it's really easy for competitive intelligence companies to bot create accounts just to get a feeling of new user growth if you're naively using auto increments. This maybe fine mom and pop sites, or sites where somehow the UID never leaves the middleware, but the reality is that using sequential IDs or hashes of them without salt aren't great for this reason.
