Should I add an external id for every table?

For example if I have these tables :

Id       Name      Gender
1        John      M
2        Doe       M

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?


  • Is it possible that your friend meant surrogate keys? – Mike Jul 9 '13 at 5:33
  • From a purely relational point of view this is a wrong thing to do. You should have your CustomerId column in CustomerPurchase and a constraint "binding" it to Customer (foreign key). Now if this design is for SAP (which i dont know) maybe you should say in your question that this design is made specifically for a SAP system – Kemoda Jul 9 '13 at 6:31
  • ok, just found this : help.sap.com/saphelp_470/helpdata/FR/9b/… i dont see why you need it in CustomerPurchase – Kemoda Jul 9 '13 at 6:35
  • @Mike If you mean this then I don't think it is. From what I can comprehend, his explanation about ExternalId is for the benefit of the user and very little for the system. – Samuel Adam Jul 9 '13 at 6:43
  • @Kemoda Actually my example is oversimplifying things. I would like to find if there any good reason for using ExternalId besides it's readibility in transactions table. – Samuel Adam Jul 9 '13 at 6:44

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.

  • 1
    I swear it took longer for me googling "layman's word" than researching your answer. So basically you're saying to choose one, and it's a bad practice to use both? – Samuel Adam Jul 9 '13 at 8:19
  • @SamuelAdam No. When one of the four conditions are met, you have to use both, the surrogate key as primary key, and the natural key as alternate key. If none of the four conditions are met, you use only the natural key, obviously as the primary key. – Tulains Córdova Jul 9 '13 at 8:31
  • @SamuelAdam By the way, I made a mistake: It's "in layman's words" (prural). – Tulains Córdova Jul 9 '13 at 8:32
  • I prefer surrogate primary keys. Related: dba.stackexchange.com/questions/6108/… – Mike Partridge Jul 9 '13 at 19:29
  • @MikePartridge The most voted answer to the question you linked, says that not always a surrogate is a good thing and explains the problems that blind approach causes. Besides, I like both surrogates and natural keys, ones in some cases, the others in other cases. – Tulains Córdova Jul 9 '13 at 19:48

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).

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

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.