External id for every relational table

Question

Should I add an external id for every table?

For example if I have these tables :

Customer
=======================================
Id       Name      Gender
---------------------------------------
1        John      M
2        Doe       M
---------------------------------------

CustomerPurchase
==========================================================================
Id       ExternalId      CustomerId            TotalQty
--------------------------------------------------------------------------
1        PO0313-0001     1                     10
--------------------------------------------------------------------------

In that example, I understand that ExternalId is required in CustomerPurchase table (for printing, etc).

But I don't see any reason why Customer table should need one as well.

FYI, the reason I asked this because a friend of mine said that SAP uses ExternalIds in their tables.

Should I use ExternalId in every table?

Am I missing any ExternalId's other usage besides for user's eye friendlier identification?

Thanks!

Answer 1

In layman's words:

Use surrogate primary keys as primary keys when:

1. There are no natural or business keys
2. Natural or business keys are bad ( change often )
3. The value of natural or business key is not known at the time of inserting record
4. Multicolumn natural keys ( usually several FK ) exceed three columns, which makes joins too verbose.

If a natural key exists that doesn't fall into the conditions listed above, for the sake of God, use it, especially if the key is an ISO standard or is issued by some respected institution, like country codes, airport or airline IATA codes, MAC addresses, car plaque numbers, IMDB movie codes, radio station call signs, etc. That would allow you to interoperate easier.

Above conditions mean surrogate keys will have to be used in many tables. But not all.

Answer 2

ExternalId seems to be nothing more than a function that provides the current SAP login.

When describing a record of activities, including identification of who performed the activity is normal. It is expected. Deciding which ID to use is a different matter.

Are you interested in recording the external user name as determined by the underlying system, or are you interested in providing the username as far as the logical application domain is concerned?

Conceivably, the purchasing company will have more than one authorized buyer. Likely, this is a common scenario. This is probably a clear case where including identification of the actor in this action record is important.

In this case, the user ID in question is not a logical internal user ID. Instead, it is an external, physical system provided user id. While utilizing the external system user id as a means of convenience for authentication is great, I would be reluctant to recommend using it as basis of internal identification for authorization questions. Instead, harmonize or translate external user ids to logical internal, application local user ids.

Recording external user ids is great because it provides an opportunity to audit system access and usage patterns. However, there is a conflict. Is a logical user that logs in from a Windows machine different from a user who logs in from a UNIX machine even when it actually the same person or service? What if the schema/database is ported/hosted/replicated to a different system, which uses different database access control and uses a different database username scheme? Instead of adding another mapping of external user id's to internal logical user id, you may have to transform past data too.

By segregating external user ids to a user table, you can choose to associate different system logins with the same or different logical users. By also preventing the bleeding of external system user id's into logical application data you provide some protection to the data in the event that future implementations provide different authentication schemes.

The third reason to isolate external usernames from internal logical domain usernames is in the case of impersonation or delegation. Sometimes a user is authorized and expected to perform actions on behalf of others, where the action should normally be viewed as being performed by delegator, but perhaps with some leading step indicating that delegation occurred and who assumed the subsequent identity (and other session pertinent information).

User
|UserId(PK)    |PersonaId    |AuthExternSAP    |GoogleOAuth2    |
|1             |1            |PO0313-0001      |                |
|2             |1            |                 |345345324       |

Customer
|CustomerId(PK)    |UserId    |
|1                 |1         |

CustomerPurchase
|CustomerPurchaseId (PK)    |CustomerId    |TotalQty    |SellableResourceId    |
|1                          |1             |10          |1                     |