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


  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
  • 2
    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.
    – mustaccio
    Commented Jul 5 at 21:53
  • 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. Commented Jul 8 at 7:35

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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