The supplier master data model: the tables, keys, and relationships most vendor masters get wrong

Search your vendor master for ACME and you get four hits. One plain, one with a city appended, one with a year and the words NEW BANK, and one spelled slightly differently by whoever created it in 2019. The usual response is to call this a data quality problem and schedule a deduplication project. That project will fail, because somebody genuinely needs each of those four rows, and the person who merges them will spend the following month explaining to procurement why deliveries are going to the wrong address. The problem sits in the model, not in the typing.

ACME has three delivery addresses, changed banks two years ago, and trades under a second legal name in Belgium. Your vendor master gives it one row. So the people who needed the other facts did what anyone would do: they made more rows and put the difference in the name field. Every duplicate supplier conversation I have been in eventually arrives at the same place, which is that the table was never shaped like the thing it was describing.

What a supplier master data model actually is

A supplier master data model is the set of entities and relationships that describe a vendor: one record for the legal entity, plus separate child records for its sites, bank accounts, tax registrations, contacts, certificates, and category assignments. The vendor master in your ERP is a single table. The model is the shape underneath it.

That distinction sounds academic until you look at what the ERP table was designed for. It was designed to pay someone. It needs an account code, payment terms, a bank account, an address, and a tax number, which is exactly one of each, because an invoice only ever needs one of each. Procurement, compliance, and logistics arrived later with questions the table has no room to answer, and rather than change the table, everyone worked around it. Most of the attention since has gone to the governance side of supplier master data, which is fair enough, since that is where the visible pain is. My argument is that you cannot govern a shape that cannot hold the facts, so the model has to come first even though it is the less interesting half.

The one-table trap

Six things about a supplier are one-to-many: its sites, its bank accounts, its tax registrations, its contacts, its certificates, and its categories. A flat vendor master gives each of them one column. When reality needs two, the user has three options, and all three are bad.

They can overwrite the existing value, which quietly destroys history and is how a bank account change becomes untraceable. They can create a second supplier record and put the difference in the name, which is the four ACME rows. Or they can give up and keep it in a spreadsheet, which is where most lead times and certificate expiry dates actually live. None of those are things a person picks on purpose. In a table with one row per supplier there is no fourth option, so the user takes whichever of the three costs them least effort this afternoon, and none of the three costs them anything at all until much later.

The second option is the expensive one, because it breaks two things at once that nobody connects back to the cause. Duplicate detection stops working, since ACME Corp and ACME Corp - ROTTERDAM are genuinely different strings and a fuzzy match that catches them will also catch two unrelated companies. And spend analysis stops working, because the four rows split one supplier relationship into four purchase histories, so the vendor you spend the most with never appears at the top of the list. Teams then negotiate contracts against a number that is a quarter of the truth.

The core model: seven entities

Seven entities cover almost every supplier requirement I have seen in mid-market implementations. The test for whether something deserves its own table is not how important it is. It is whether a supplier can have two of them.

Notice the owner column. Four different functions own pieces of one supplier, which is the real reason this data fragments: each of them built the piece they needed in the system they controlled. The model does not fix that on its own, but it gives every function a place to put its fields without inventing another vendor record.

The SQL

The parent table stays small. Anything that can occur twice has already left, so what remains is the identity of the legal entity and its lifecycle. The persisted computed column exists to give the duplicate constraint something stable to bite on.

CREATE TABLE dbo.Supplier (
    SupplierId       INT IDENTITY(1,1) NOT NULL,
    SupplierNumber   VARCHAR(20)   NOT NULL,   -- business key, shown to users
    LegalName        NVARCHAR(200) NOT NULL,
    CountryCode      CHAR(2)       NOT NULL,   -- ISO 3166-1 alpha-2
    LifecycleStatus  VARCHAR(20)   NOT NULL,   -- draft|active|blocked|archived
    ParentSupplierId INT           NULL,       -- legal parent, not spend rollup
    OwnerUserId      INT           NOT NULL,
    CreatedAt        DATETIME2(0)  NOT NULL CONSTRAINT DF_Supplier_CreatedAt DEFAULT SYSUTCDATETIME(),
    NameKey AS LOWER(REPLACE(REPLACE(REPLACE(LegalName, ' ', ''), '.', ''), ',', '')) PERSISTED,
    CONSTRAINT PK_Supplier PRIMARY KEY CLUSTERED (SupplierId),
    CONSTRAINT UQ_Supplier_Number UNIQUE (SupplierNumber),
    CONSTRAINT FK_Supplier_Parent FOREIGN KEY (ParentSupplierId)
        REFERENCES dbo.Supplier (SupplierId)
);

-- Soft duplicate guard: same normalized name in the same country.
-- Scoped to non-archived rows so historic records do not block new ones.
CREATE UNIQUE INDEX UX_Supplier_NameCountry
    ON dbo.Supplier (NameKey, CountryCode)
    WHERE LifecycleStatus <> 'archived';

Sites carry a type, and the type is what makes the four ACME rows unnecessary. One supplier, three sites, each flagged for what it is allowed to do. The filtered unique index enforces exactly one default per type without forbidding the others.

CREATE TABLE dbo.SupplierSite (
    SupplierSiteId INT IDENTITY(1,1) NOT NULL,
    SupplierId     INT           NOT NULL,
    SiteType       VARCHAR(20)   NOT NULL,   -- order|ship_from|remit_to|registered
    SiteCode       VARCHAR(20)   NOT NULL,
    AddressLine1   NVARCHAR(200) NOT NULL,
    PostalCode     NVARCHAR(20)  NULL,
    City           NVARCHAR(100) NULL,
    CountryCode    CHAR(2)       NOT NULL,
    IsDefault      BIT           NOT NULL CONSTRAINT DF_SupplierSite_IsDefault DEFAULT 0,
    IsActive       BIT           NOT NULL CONSTRAINT DF_SupplierSite_IsActive DEFAULT 1,
    CONSTRAINT PK_SupplierSite PRIMARY KEY CLUSTERED (SupplierSiteId),
    CONSTRAINT UQ_SupplierSite_Code UNIQUE (SupplierId, SiteCode),
    CONSTRAINT FK_SupplierSite_Supplier FOREIGN KEY (SupplierId)
        REFERENCES dbo.Supplier (SupplierId)
);

-- Exactly one default site per type, per supplier.
CREATE UNIQUE INDEX UX_SupplierSite_Default
    ON dbo.SupplierSite (SupplierId, SiteType)
    WHERE IsDefault = 1 AND IsActive = 1;

Bank accounts are the one table where I would argue the design is not a preference. Make the rows effective-dated and never update an account number in place. A changed IBAN closes the old row and opens a new one, which means the question of what account we paid on 14 March has an answer, and the question of who changed it has one too.

CREATE TABLE dbo.SupplierBankAccount (
    SupplierBankAccountId INT IDENTITY(1,1) NOT NULL,
    SupplierId    INT          NOT NULL,
    Iban          VARCHAR(34)  NOT NULL,
    Bic           VARCHAR(11)  NULL,
    CurrencyCode  CHAR(3)      NOT NULL,   -- ISO 4217
    ValidFrom     DATE         NOT NULL,
    ValidTo       DATE         NULL,       -- NULL = currently valid
    ApprovedBy    INT          NULL,       -- NULL until second-person approval
    CONSTRAINT PK_SupplierBankAccount PRIMARY KEY CLUSTERED (SupplierBankAccountId),
    CONSTRAINT FK_SupplierBankAccount_Supplier FOREIGN KEY (SupplierId)
        REFERENCES dbo.Supplier (SupplierId),
    CONSTRAINT CK_SupplierBankAccount_Period CHECK (ValidTo IS NULL OR ValidTo > ValidFrom)
);

-- One open account per supplier per currency at any moment.
CREATE UNIQUE INDEX UX_SupplierBankAccount_Open
    ON dbo.SupplierBankAccount (SupplierId, CurrencyCode)
    WHERE ValidTo IS NULL;

Tax registrations follow the same shape and carry the constraint that does the most duplicate prevention of anything in the model. One tax number per country, unique across all suppliers, enforced at insert. A user creating a vendor that already exists finds out immediately, which is the only moment it is cheap to fix.

CREATE TABLE dbo.SupplierTaxRegistration (
    SupplierTaxRegistrationId INT IDENTITY(1,1) NOT NULL,
    SupplierId  INT          NOT NULL,
    CountryCode CHAR(2)      NOT NULL,
    TaxNumber   VARCHAR(30)  NOT NULL,
    IsPrimary   BIT          NOT NULL CONSTRAINT DF_SupplierTaxReg_IsPrimary DEFAULT 0,
    CONSTRAINT PK_SupplierTaxRegistration PRIMARY KEY CLUSTERED (SupplierTaxRegistrationId),
    CONSTRAINT FK_SupplierTaxReg_Supplier FOREIGN KEY (SupplierId)
        REFERENCES dbo.Supplier (SupplierId),
    -- The hard duplicate guard: nobody else may hold this number in this country.
    CONSTRAINT UQ_SupplierTaxReg_Number UNIQUE (CountryCode, TaxNumber)
);

Choosing the supplier identifier

Someone will propose making the VAT number the primary key. It looks clean: globally meaningful, externally verifiable, no meaningless integers. Do not do it. Tax numbers change when a company restructures, they are not unique across countries, some suppliers hold several, and sole traders in several jurisdictions have none at all. A primary key that can change or be missing pushes that problem into every foreign key that references it, and the identifier is the one decision you cannot reverse cheaply.

Surrogate integer as the key, tax number behind a unique constraint. You get the duplicate prevention without the fragility. The ERP vendor numbers, the procurement portal IDs, and whatever the logistics system calls the same company then live in a cross-reference table, one row per system per supplier. That table is not optional decoration. It is the thing that lets you answer whether vendor 4471 in the ERP and supplier ACM-0012 in the portal are the same company, and every integration you build afterwards depends on the answer.

Where item and supplier master data meet

Supplier item master data is the relationship between a vendor and something you buy from them: their part number for it, the lead time, the minimum order quantity, the price breaks, and whether they are the preferred source. It belongs to neither master. One item comes from four suppliers, one supplier delivers hundreds of items, and the facts that matter are true only for the pair.

This is the table most implementations skip, and skipping it has a signature. Lead times end up in a planner's spreadsheet, supplier part numbers get typed into purchase order free-text fields, and the answer to who else can supply this part is somebody's memory. Then the supplier has a fire and the answer matters within a day.

CREATE TABLE dbo.SupplierItem (
    SupplierItemId  INT IDENTITY(1,1) NOT NULL,
    SupplierId      INT           NOT NULL,
    ItemId          INT           NOT NULL,
    SupplierPartNo  NVARCHAR(50)  NOT NULL,   -- their number, not yours
    LeadTimeDays    SMALLINT      NULL,
    MinOrderQty     DECIMAL(18,4) NULL,
    OrderUomCode    VARCHAR(10)   NULL,       -- how they sell it, not how you stock it
    IsPreferred     BIT           NOT NULL CONSTRAINT DF_SupplierItem_Pref DEFAULT 0,
    CONSTRAINT PK_SupplierItem PRIMARY KEY CLUSTERED (SupplierItemId),
    CONSTRAINT UQ_SupplierItem_Pair UNIQUE (SupplierId, ItemId),
    CONSTRAINT FK_SupplierItem_Supplier FOREIGN KEY (SupplierId)
        REFERENCES dbo.Supplier (SupplierId)
);

-- One preferred source per item.
CREATE UNIQUE INDEX UX_SupplierItem_Preferred
    ON dbo.SupplierItem (ItemId) WHERE IsPreferred = 1;

One detail in there causes more trouble than the rest combined. OrderUomCode is the unit the supplier sells in, which is not always the unit you stock in, and the conversion between them belongs to the item rather than this table. Get that wrong and you order twenty-four times what you meant to. Also worth deciding early: whether payment terms sit on the supplier or can be overridden per item, because procurement will eventually ask for the override. This table has enough in it to deserve its own treatment, which it gets in supplier item master data: the manufacturer part number, sourcing rank, and what your ERP already calls the record.

The parent-child problem

The ParentSupplierId column in the parent table does one job: it records legal ownership, so you know that ACME Nederland B.V. is a subsidiary of ACME Group. That is a fact about company structure, it changes rarely, and a self-referencing column handles it fine.

What it cannot do is spend rollup, and this is where supplier models usually go wrong on the second attempt rather than the first. Procurement wants total spend with the ACME group, which follows legal ownership. Finance wants spend by paying entity, which follows the invoicing structure. Category management wants spend by commodity, which cuts across both. These are three different trees over the same suppliers, and the moment you try to serve all three from one parent column, one of them starts counting the same revenue twice. Keep the legal parent where it is, as a column on the supplier, and give every other tree its own named hierarchy with a membership table underneath it. You will end up with two or three of those, which sounds like more work than it is, since each one is a small table and nobody has to reconcile them against each other.

What to make mandatory at creation

Standardization documents tend to run to forty fields, and the length is why nobody follows them. Six fields, enforced at the moment of creation, prevent more damage than a forty-field guideline enforced by nobody.

Everything else can be filled in later by the function that cares about it. Certificates, categories, and extra contacts are all things a supplier can trade without on day one, and blocking creation until compliance has uploaded an ISO certificate is how people learn to create suppliers in the spreadsheet instead.

Building this without writing the DDL

The SQL above is the model expressed as tables, which is the right form if you are extending a database you already own. In a master data platform you express the same thing as entities and attributes instead, and the seven tables become seven entities in one Supplier model, with the child entities referencing the parent through a domain attribute. Country, currency, and commodity category stop being free-text columns and become their own reference entities, which is what makes them selectable rather than typeable.

In Primentra that model takes an afternoon to build, and two things come with it that are awkward to write by hand. Bank accounts can require approval before a change goes live, so a new IBAN is a proposed change until a second person reviews it against the old value. And every field change is recorded with who, when, and what it was before, which is the history the effective-dated design above is trying to reproduce manually. If you are coming off Microsoft MDS, this is also the point where the model matters most: a flat vendor entity migrated as-is carries the four ACME rows into the new system, and the migration is the cheapest moment you will ever have to split them.

Common questions