# A Supplier Name Is Not a Primary Key: Designing a Commodity Supplier Registry

A procurement database can contain three supplier records and still be dealing with one legal entity. One import uses an abbreviated trading name, another preserves the registered name, and a third transliterates it. Joining purchase orders on a cleaned-up name can also make the opposite mistake: treating two different entities as one.

For commodity software, that ambiguity can spread into product approvals, supplier specifications and inventory attribution. The useful engineering question is not “How similar are these names?” It is “What evidence supports linking these records to the same entity?”

This article proposes a small registry design for that decision. **All examples are synthetic. The design is illustrative and does not describe a deployed Zenith Eclipse system or a real supplier.**

## Separate the entity from its account in each system

Use an internal, immutable identifier for the entity. Keep the ERP vendor number, procurement-portal ID and source-system name as separate references.

A single entity may have multiple vendor accounts: different purchasing organizations, currencies or onboarding histories can produce separate operational records. Those accounts should not automatically become separate companies. Equally, two entities using the same brand should not automatically become one company.

A minimal model has three layers:

| Layer | Identifies | Example, entirely synthetic |
| --- | --- | --- |
| Legal entity | The organization represented by a registry record | Internal UUID |
| External identifier | An identifier within a named issuing authority or scheme | `demo-register / demo-authority / EX-1042` |
| Source supplier account | A record in a particular operational system | `purchasing-demo / vendor-0081` |

An [LEI](https://www.gleif.org/en/organizational-identity/lei-vlei/the-legal-entity-identifier-lei) is one possible external identifier: GLEIF describes it as a unique 20-character alphanumeric identifier linked to legal-entity reference information. Design the registry so an LEI can be added when available; do not make every supplier record depend on having one. An identifier lookup also does not establish product quality, commercial authorization or available stock.

For a concrete product-data distinction, Zenith Eclipse's [soft wheat requirements](https://zenitheclipse.com/soft-wheat-supplier/) and [durum wheat requirements](https://zenitheclipse.com/durum-wheat-supplier/) describe different end uses and acceptance parameters. In this proposed registry, those belong in separate product-approval records linked to the supplier identity; matching the entity must not copy approval from one product to another.

## Put uniqueness where it has meaning

The following PostgreSQL fragment expresses a narrow invariant: an accepted external identifier resolves to one internal entity.

```sql
CREATE TABLE legal_entity (
    entity_id uuid PRIMARY KEY,
    legal_name text NOT NULL
);

CREATE TABLE entity_identifier (
    scheme text NOT NULL CHECK (scheme <> ''),
    authority text NOT NULL CHECK (authority <> ''),
    identifier text NOT NULL CHECK (identifier <> ''),
    entity_id uuid NOT NULL REFERENCES legal_entity(entity_id),
    PRIMARY KEY (scheme, authority, identifier)
);

CREATE TABLE source_supplier (
    source_system text NOT NULL,
    source_key text NOT NULL,
    entity_id uuid REFERENCES legal_entity(entity_id),
    PRIMARY KEY (source_system, source_key)
);
```

The source account can exist before its entity is resolved. A missing identity remains missing; it should not be assigned a shared value such as `UNKNOWN` that accidentally joins unrelated suppliers.

[PostgreSQL's constraint documentation](https://www.postgresql.org/docs/current/ddl-constraints.html) explains that primary keys enforce uniqueness and non-null values, while foreign keys maintain references between tables. Ordinary unique constraints treat null values as distinct by default. Explicitly choosing the key and nullability avoids depending on accidental import behavior.

This fragment is a starting point, not a complete migration. It assumes the identifier scheme does not reassign a value to a different entity. Verify that assumption for each source; a scheme with identifier reuse needs a model that includes validity periods. Production code also needs controlled normalization, permissions, audit history and source-account indexes appropriate to its queries.

## Preserve the evidence behind a match

Store imported names exactly as received, alongside a separate search-normalized form. Keep the source, observation time and applicable document or registry reference. Do not replace the original spelling after stripping punctuation or legal suffixes.

A reasonable matching pipeline can then work in stages:

1. Validate the incoming structure and identify its source system.
2. Look for an existing source-account mapping.
3. Compare usable external identifiers within their correct namespaces.
4. Generate name-based candidates when stronger identifiers are absent.
5. Route contradictory or ambiguous evidence for review.

Name similarity should rank candidates, not authorize a merge. Even a strong identifier match requires checking the evidence: a copied spreadsheet may contain the right number beside the wrong company name.

[JSON Schema](https://json-schema.org/understanding-json-schema/reference/object) can help enforce an import contract. Declaring fields under `properties` does not make them mandatory; use `required` deliberately. An `additionalProperties` policy can expose unexpected fields such as a misspelled identifier property. These checks validate the payload's structure, not whether the supplied identity is true.

## Treat a merge as a reviewable change

Avoid a “merge and delete” button that destroys the losing record's history. Record the decision, its evidence and the old-to-new mapping. Keep past purchase documents tied to the identity information recorded when they were issued.

For current reporting, resolve approved mappings through a canonical-entity view. For historical investigation, retain the original source account and document snapshot. This adds complexity, but makes it possible to explain why two orders were grouped together—and to correct a mistaken grouping without rewriting every historical document.

Use a transaction for the mapping change and check whether another reviewer has changed either record since the review began. A uniqueness conflict should open a reconciliation case, not trigger an automatic overwrite of the existing entity assignment.

## Test false merges before celebrating fewer duplicates

Build a small synthetic fixture set with expected decisions:

| Input | Expected result |
| --- | --- |
| Different names, verified identical identifier | Review evidence and link to the existing entity |
| Identical names, different verified registrations | Keep separate entities |
| Identifier conflicts with the existing mapping | Hold for reconciliation |
| No reliable identifier | Preserve an unresolved source account |
| Same source record imported twice | Keep one source-account identity |

Track incorrect merges and unresolved records as well as duplicate reduction. A lower supplier count is not necessarily better data.

The practical takeaway is to keep identity, operational accounts and matching evidence separate. Names help people search. Stable identifiers and reviewable mappings let systems join records without pretending that similarity is proof.

---

**About the publisher:** [Zenith Eclipse Co](https://zenitheclipse.com/) works across agricultural commodities, international logistics and machinery sourcing.