External IDs in Database Design: A Practical Guide

External IDs Are Not Your IDs: Modeling Identity Across Systems

A backend team adds one column to the customer table:

stripe_customer_id

Six months later, another integration arrives:

hubspot_contact_id

Then the company migrates its support platform, imports customers from an acquired product, connects an accounting system, and starts accepting records from enterprise clients.

The once-simple customer table now contains a growing collection of identifiers:

stripe_customer_id, hubspot_contact_id, quickbooks_customer_id, legacy_customer_id, partner_customer_id.

Nothing is technically broken yet. That is what makes this database problem dangerous.

The deeper issue is that the schema has confused two very different ideas: who a customer is inside your system and how another system refers to that customer.

This distinction becomes critical in databases that import, synchronize, merge, or exchange data. External identifiers are not merely extra fields. They are relationships between identities maintained by different systems.

Model them that way, and integrations become easier to reason about. Ignore the distinction, and the database gradually becomes a map of every API the application has ever touched.

Start by investigating the identifiers you actually have

Imagine a SaaS company whose internal database contains this customer:

customer.id = 7421

The same organization appears elsewhere as:

• Stripe customer cus_R91K2
• HubSpot company 18420937
• QuickBooks customer 391
• an old billing database record C-000884

A common schema design response is to add those identifiers directly to the customer table.

That can be acceptable when an application has exactly one permanent integration. But before making that assumption, investigate the data rather than the API documentation.

Ask questions such as:

• Can one internal customer have multiple records in the external system?
• Can the same external identifier appear in multiple accounts or tenants?
• Can identifiers be reused after records are deleted?
• Will the integration ever be replaced?
• Can multiple integrations of the same provider exist?
• Do imported records exist before an internal customer is created?
• Can two imported records later be discovered to represent the same customer?

These questions frequently reveal that an external ID is not an attribute of the customer at all.

It is an identifier valid inside a particular namespace.

The namespace is part of the identity

Suppose two businesses connect their QuickBooks accounts to your application.

Both external systems contain a customer whose ID is 391.

If the database stores only:

external_customer_id = 391

the value is ambiguous.

The actual identity might be closer to:

(provider = quickbooks, connection = account_17, external_id = 391)

That combination tells us something important: identifiers exist inside a context.

The same principle appears everywhere in database engineering. An employee number may only be unique within a company. A product SKU may only be unique within a supplier. A username may only be unique within a tenant. A laboratory sample number may only be unique within a research project.

When investigating a database schema, do not ask only whether a field is unique.

Ask: unique within what boundary?

That question often exposes relationships that an ER diagram initially hides.

A stronger model separates internal identity from external identity

Instead of adding integration-specific columns to customer, consider separating them into a dedicated relationship.

An internal customer might remain simple:

customer(id, name, status, created_at)

A connected external system might be represented as:

external_connection(id, provider, account_name, tenant_id)

Then mappings can live in another relation:

customer_external_identity(id, customer_id, connection_id, external_id)

The important relationship is now explicit:

A customer can have zero, one, or many external identities, and every external identity belongs to a particular connection.

A useful uniqueness rule might be:

UNIQUE(connection_id, external_id)

rather than assuming external_id itself is globally unique.

This schema also tells a clearer story in an ER diagram. Instead of seeing Stripe, HubSpot, accounting software, and future integrations embedded as columns inside one business entity, you see the actual concept being modeled: identity mapping between systems.

When exploring alternatives visually, an ER diagram modeling tool makes this particularly useful because you can compare the growing-column model with the relationship-based model before changing production tables.

Now test the model against messy records

A schema that works only for clean examples has not been tested yet.

Consider these imported records:

Stripe

cus_A91 | Acme Research Ltd | [email protected]

HubSpot

90381 | Acme Research | [email protected]

Legacy database

ACM-144 | ACME Research Limited | [email protected]

Are these three identities the same customer?

Probably.

But the external IDs themselves do not prove it.

This exposes another useful modeling principle: identity mapping and identity matching are different problems.

Once the application has determined that all three records correspond to internal customer 7421, the mappings can be stored confidently.

Before that determination has been made, treating the records as already belonging to one customer may destroy information about their original source.

For data imports, migrations, and synchronization pipelines, it can therefore be useful to preserve raw source records separately from resolved business entities.

For example:

imported_customer_record(id, connection_id, external_id, raw_name, raw_email, imported_at)

A resolution process can later associate that imported record with:

customer_id = 7421

This gives developers somewhere to investigate disagreements instead of overwriting the source data during import.

Challenge the assumption that matching fields imply matching identities

Email addresses are often used as shortcuts when integrating databases.

Two records have the same email address, so they must represent the same person.

That assumption works until:

• a shared finance email represents an entire company;
• an employee changes companies;
• a user updates their email in only one system;
• a historical record contains an old address;
• a typo creates two apparently different identities;
• multiple systems normalize email addresses differently.

The same problem occurs with names, phone numbers, SKUs, addresses, and company registration details.

These values are useful evidence for matching. They are not necessarily identity.

Database research matters here because the right answer often cannot be derived from a whiteboard session. You need to inspect real records.

Take a sample of imported data and calculate how often proposed matching fields disagree. Search for duplicate emails. Look for external IDs appearing under more than one tenant. Find customers with multiple external records. Examine records that users manually merged.

The strange cases tell you more about the required database architecture than the clean ones.

The weak approach creates integration-shaped schemas

Putting stripe_customer_id on a customer table is not inherently bad.

The problem begins when the architecture assumes every external system deserves another permanent column.

After enough integrations, the database starts reflecting vendor history instead of business structure.

A customer entity might contain twenty nullable integration fields, even though most customers use only two integrations.

That causes several problems.

New providers require schema changes. Adding another integration now means modifying a core business table.

Multiple accounts become awkward. What happens when a customer is represented in two Salesforce organizations? salesforce_id_1 and salesforce_id_2 are warning signs.

Ownership becomes unclear. Developers may begin treating imported identifiers as canonical simply because they are conveniently stored beside internal fields.

Historical migrations leave debris. A provider removed three years ago may still have dedicated columns because deleting them feels risky.

Uniqueness becomes difficult to express correctly. The database may enforce uniqueness on a value that is only unique within an external account.

This is how a small implementation shortcut becomes long-term schema architecture.

Do not turn the generic model into an untyped junk drawer

There is an opposite mistake.

Once developers realize that external identities can be generalized, they sometimes create something like:

external_reference(entity_type, entity_id, provider, external_id)

Then every imaginable object goes into it: customers, invoices, products, orders, employees, shipments, subscriptions, warehouses, and support tickets.

The model is flexible, but flexibility has a cost.

A database may no longer be able to enforce ordinary foreign keys cleanly. Developers must interpret entity_type correctly. Deleting an entity can leave orphaned references. Queries become more generic but less understandable.

So the goal is not to eliminate every integration-specific structure.

The goal is to model the concept at the correct level.

If customer identity mapping is important enough to the domain, a dedicated customer_external_identity relationship may be clearer than a universal polymorphic table.

If products have completely different reconciliation rules, they may deserve product_external_identity.

Good data modeling is not a competition to create the fewest tables. It is an attempt to make important rules visible.

Preserve the source when imported facts can disagree

External IDs become even more important when imported systems disagree about attributes.

Suppose your internal customer record says:

name = "Acme Research Ltd"

HubSpot says:

name = "Acme Research"

QuickBooks says:

name = "ACME RESEARCH LIMITED"

Which one is correct?

The answer depends on who owns that field.

Your application might treat the internal customer name as canonical while using QuickBooks as the source of truth for accounting codes and Stripe as the source of truth for payment status.

External identity modeling therefore connects directly to data ownership.

For every synchronized field, investigate:

• which system can create it;
• which system can change it;
• which copy is authoritative;
• whether synchronization is one-way or two-way;
• what happens when values conflict.

Without these decisions, synchronization logic silently becomes the real database architecture.

Model migrations as identity changes, not only data copies

External identity modeling becomes especially useful during system migrations.

Imagine moving from billing platform A to billing platform B.

Customer 7421 may temporarily have both:

(billing_a, old-account, C8821)

and:

(billing_b, new-account, 559021)

The customer did not become a new customer.

Its external representation changed.

If external IDs are stored as relationships, both mappings can coexist during migration. The application can record when one became inactive without rewriting the customer’s internal identity.

This separation makes database migrations easier to reason about because the schema distinguishes the stable business entity from its changing representations elsewhere.

A practical investigation workflow

Before designing an integration-heavy database schema, collect actual examples instead of starting with provider names.

1. List the systems that assign IDs. Include internal applications, third-party APIs, imported spreadsheets, legacy databases, and partner feeds.
2. Record the namespace of every identifier. Determine whether uniqueness belongs to the provider, account, tenant, project, organization, or dataset.
3. Find one-to-many cases. Search for internal entities represented by multiple external records.
4. Find ambiguous matches. Test email addresses, names, SKUs, or other proposed matching fields against real data.
5. Separate matching from mapping. Decide how uncertain imported records exist before they are linked to canonical entities.
6. Document ownership. Determine which system controls each synchronized attribute.
7. Test migration scenarios. Ask what the schema looks like when one provider is replaced without changing internal identity.

This is the kind of database research that should happen before an integration becomes permanent schema.

If you are comparing possible models, the broader database design resources on DBDesigner can help connect identity decisions with relational modeling, schema design, and ER diagrams.

The schema should know who owns the name

An external identifier may look like a harmless string or integer.

But once several systems participate in the same business process, that identifier carries architecture with it.

It tells you who created a record, where its identity is valid, how systems correspond, and what must survive when an integration changes.

The most useful distinction is simple:

Your database ID identifies the entity your system owns. An external ID identifies how somebody else’s system refers to it.

Those may coincide for years. They are still not the same fact.

Designing that distinction explicitly prevents vendor-specific columns from taking over core tables, makes migrations safer, preserves source context, and gives engineers a better place to investigate conflicting records.

The next time an integration request says, “We just need somewhere to store their ID,” do not start by adding a column.

First ask whose ID it is, where it is unique, how long it will remain valid, and what relationship it represents.

Those questions lead to a database schema that can survive the integration that comes after this one.