I have had this fight so many times ... "we don't need to generate a random key, we can just use this 'unique' identifier" where the unique identifier is always some form of PII.

Some form of PII that can change too. Natural keys are a no go for me.

> there is a segment of the database side that thinks natural keys are better than artificial keys.

Aren't those guys hammered nearly daily (at least weekly) with one real-world example after another about how natural keys aren't unique?

I’m not sure it does the job of a natural key though. When you renew a card you can keep the number but get new exp/security number which should probably be considered a different record.

The bigger issue: I also wouldn’t be quick to assume I know the idiosyncrasies of all card systems, like whether they reuse numbers.

The natural step is a multicolumn key (e.g. the key includes all three data points).

I've seen tables where primary keys were 7-10 columns long. It's a nightmare to work with when joining.

I like natural keys... if you can prove that they're actually immutable and unique for the thing they're representing. Credit card number is a decent natural key for a table of payment instruments, not for users. Even for a natural-key-believer, users pretty much always need a synthetic ID, because anything you might possibly believe to be constant about humans turns out not to be.

And what if you need to be able to refer to those instruments, e.g. check or use them, but at the same time not expose credit card number to whatever entity needs to do the check?

How do you store the payment instrument for a certain purchase?

Surely the next thought is “I don’t want customer CC leaking” regardless what your background is?

When you introduce another ID that, maps 1:1 to the old one what do you get?

With the external id I get a single layer of obfuscation. If the external id is leaked they still don’t have any data that allows me to make a CC purchase. And if you designed it correctly, you could invalidate the id. ie a token that expires.

If the external id represents a single costumer uniquely, it still counts as personal information. That you hand out money by giving one id, but not to another is really arbitrary and doesn't make any sense. Knowing an identifier of another person should not allow you to withdraw money either way.

But you can’t hand out money with either value. The external ID is only valid in the system it was generated in.

And as I said, you can design it so that the ID expires periodically.

> But you can’t hand out money with either value.

Then there is no problem using the natural key?