Using internal or external ids when storing external data

Question

When storing external data, I usually have two ids. One is external and one is internal. I inevitably need to keep indexes on both ids for lookups. Examples:

• Storing stripe customers, products, ids
• Storing shopify products, images, variants, etc.
• Any external data really

QUESTIONS:

1. Is this just a necessary part of storing external data or can I be using the external ids only?

2. Which ids should I use as relational keys between my tables internally?

   • External ids
   • Internal ids
   • Both internal and external ids

3. In terms of column naming, is it common to have:

   • id and external_id
   • internal_id and external_id; or
   • id and shopify_product_id

• mustaccio: This question is similar to: Surrogate key vs Natural key. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem.
• Laurenz Albe: This calls for opinions, but my opinion is that you need no extra artificial "internal" key as long as you always have a unique, immutable "external" key.

Answer 1

Having written many applications with such databases, I would always use your internal id within the database. There are several reasons (not all will apply to you)

• The internal id is smaller and usually numeric, whereas you need to store the external id as a string.
• The external id can change, when the store decides on a different supplier. The internal id needs to remain the same.
• Some stores deal with multiple suppliers, each having their own external ids.

For naming, call them whatever feels natural to you.