You'll regret using natural keys

Yes you can.

You can support the old cus_ prefix as well as the new org_ prefix, but always return org_ from now on

Yes it will. Your changes will be confined to only the table(s) where the natural key is present, not spread across every table where there's a foreign key.

Of course you will still have to deal with the reality that the natural key is now not unique, and model reality, but your implementation work in doing so is far simpler.

In more years than I care to count I've regretted someone using natural keys as a primary key for a table many times, and surrogates never.

I have not seen clear guidelines about whether an organization's surrogate keys for persons are considered PII. (And this ambiguity has frustrated me for some time as I am unclear whether to take an aggressive or conservative view on labelling PII where I work.) When I have read the guidelines, it seems ambiguous but on balance I think it disagrees with your implied claim (that PII is not present in a table that uses a surrogate foreign key to a person/user.)

The definition of PII per NIST https://nvlpubs.nist.gov/nistpubs/legacy/sp/nistspecialpubli... is:

"Any information about an individual maintained by an agency, including (1) any information that can be used to distinguish or trace an individual‘s identity, such as name, social security number, date and place of birth, mother‘s maiden name, or biometric records; and (2) any other information that is linked or linkable to an individual, such as medical, educational, financial, and employment information."

A surrogate key associated with a person is arguably "any other information that is linked or linkable to an individual" meaning that all tables containing the surrogate key remain PII and remain "infected". It's true the surrogate key only allows linkability within the context of the data ecosystem in which it resides, but such distinctions (of "internal" to the system vs "external from the system") are not made in the language of the definition. Additionally, from a pure risk and PII disclosure impact, usually the whole database gets dumped, not just the "non-person" tables. If you have financial/medical transactions in table "A" and a personal numeric ID linking data to a person table "B", both tables contain PII, right?

From a privacy standpoint, if you can SQL JOIN the data to trace the person involved either within your system or even data reasonably obtainable outside your system, (or if an attacker can), it's PII.

Intranet IP addresses are called "linked PII" in section 3.2.2 of the above NIST guidelines for example, and NIST does define some related terms that would seem to apply to surrogate keys like: * Distinguishable Information: Information that can be used to identify an individual. * Linkable Information: Information about or related to an individual for which there is a possibility of logical association with other information about the individual. * Linked Information: Information about or related to an individual that is logically associated with other information about the individual.

As a data engineer, the above interpretation means PII is in a zillion tables and labeling a table with a boolean indicator yes/no isn't that helpful. But as a policy person, NIST seems to be recommending gauging PII more at the system (not table) level and with a PII Confidentiality Impact Level of low/medium/high that takes into account the context and overall risk and that seems sensible. From a data cataloging standpoint (gauging what's "infected" to use your term), I think it's probably helpful to identify particular transactional tables or personal table as having "high" PII disclosure impact vs "low"; the presence of "infection" from a virus ("PII") is mostly irrelevant in a sufficiently large system where viruses/some PII is inevitable but what matters is the severity/impact. A zillion tables will be "low" (e.g. if most tables have audit column saying which user last changed a particular record) but certain transactional or user tables may be "high" and should be recognized as such and the focus of any risk discussions with the business or legal or breach notifications to customers or what have you.

Going back to your original point, I don't think the choice of natural vs surrogate key impacts the PII risk of the system or even its individual tables. I would slightly concede that a surrogate key (which in general I am in favor of) would make it easier to reduce a particular individual's PII from a system by concentrating it in one or a small number of tables with names, etc. which might be helpful for enabling GDPR right to be forgotten or something. But the degree of that PII elimination from a system by blanking out or archiving a particular user/person record is not necessarily reducing the PII for them to 0 at least definitionally unless the transactional records themselves are also removed as the AOL 2006 search data scandal demonstrated (where a woman identified solely by a surrogate key was able to be identified from her search term transactions alone.) (Legally there would appear to be carveouts around transactional deletion for some financial transaction records and backups, but IANAL...)