Supplier item master data: the record that belongs to neither master

You have an item master. You have a supplier master. Both are in reasonable shape, both have an owner, and neither of them can tell you what ACME calls part 4471, how long they take to ship it, or who else could supply it if ACME stopped answering the phone.

That information exists. It is in a planner's spreadsheet, in the free-text notes on last year's purchase orders, and in the head of the buyer who has covered that category since 2019. What it is not in is a table anybody governs.

What supplier item master data is

Supplier item master data is the record of one supplier's relationship to one item you buy: their part number for it, the lead time, the minimum order quantity, the unit they sell in, and where they rank as a source. It belongs to neither master, because every fact in it needs both sides to be true.

Procurement people know this record well and rarely call it master data. They call it the info record, or the vendor item, or just “the sourcing data”. That naming gap is most of why it goes ungoverned: the team that maintains it does not think of itself as maintaining master data, and the team that governs master data has usually never opened the screen.

The cardinality is the whole argument

One item comes from four suppliers. One supplier sells you nine hundred items. Put the lead time on the item and you have one column for four different quoted numbers. Put the supplier part number on the supplier and you have one column for nine hundred parts.

So people compromise, and the compromise has a recognisable shape. A PreferredSupplier column appears on the item master, holding the vendor code of whoever happened to be preferred when the field was created. It answers one question badly and the other three not at all, and because it is populated, it looks like the problem was solved.

The same argument shapes the two masters either side of it. If your item master has collapsed its levels into one flat table, or your vendor master has one row per delivery address, the relationship record inherits both problems. Those are worth fixing first: the product master data model and the supplier master data model.

What your ERP already calls it

Every ERP has this record. None of them agree on where to put it, which matters the moment you build a master data layer that has to feed more than one of them.

The SAP row is the one that catches people. A purchasing info record is scoped to a plant and a purchasing organisation, so the same supplier and the same item can have several, with different lead times. If you model the relationship as one row per pair and your source system models it as one row per pair per plant, you will lose data on the way in and not notice until a planner asks why the Rotterdam lead time is showing the Antwerp number.

The test for what goes in it

A fact belongs in this record if it stops being true when you swap out either side. That single question settles most of the arguments about scope, including the ones that otherwise run for a fortnight.

The table

Two columns do work that is easy to miss, so read those first: ManufacturerPartNo and SourcingRank.

CREATE TABLE dbo.SupplierItem (
    SupplierItemId     INT IDENTITY(1,1) NOT NULL,
    SupplierId         INT           NOT NULL,
    ItemId             INT           NOT NULL,
    SupplierPartNo     NVARCHAR(50)  NOT NULL,  -- what they call it
    ManufacturerId     INT           NULL,      -- who made it, if not them
    ManufacturerPartNo NVARCHAR(50)  NULL,      -- same across distributors
    LeadTimeDays       SMALLINT      NULL,
    MinOrderQty        DECIMAL(18,4) NULL,
    OrderUomCode       VARCHAR(10)   NULL,      -- how they sell it
    QtyPerOrderUom     DECIMAL(18,6) NULL,      -- their pack size, in your base unit
    SourcingRank       TINYINT       NULL,      -- 1 = primary, 2 = second source
    ValidFrom          DATE          NOT NULL,
    ValidTo            DATE          NULL,
    CONSTRAINT PK_SupplierItem PRIMARY KEY CLUSTERED (SupplierItemId),
    CONSTRAINT FK_SupplierItem_Supplier FOREIGN KEY (SupplierId)
        REFERENCES dbo.Supplier (SupplierId),
    CONSTRAINT FK_SupplierItem_Item FOREIGN KEY (ItemId)
        REFERENCES dbo.Item (ItemId),
    CONSTRAINT CK_SupplierItem_Validity
        CHECK (ValidTo IS NULL OR ValidTo > ValidFrom)
);

-- One primary source per item at a time. Second sources are unconstrained.
CREATE UNIQUE INDEX UX_SupplierItem_Primary
    ON dbo.SupplierItem (ItemId)
    WHERE SourcingRank = 1 AND ValidTo IS NULL;

Note what the validity columns cost you. The pair of supplier and item is no longer unique, because the same pairing can appear several times over its history, so a unique constraint on the two foreign keys has to go. People add ValidFrom and ValidTo to an existing table without removing that constraint, and then cannot work out why the second row will not insert. If you want the fuller treatment of dated rows, slowly changing dimensions in master data covers the shapes and their costs.

Three ways this record goes wrong

One column holds the supplier part number and the manufacturer part number

Three distributors sell you the same Siemens contactor. Siemens calls it 3RT2015-1BB41. Distributor A lists it as SIE-3RT2015, distributor B has it under an eight-digit catalogue number of their own, and distributor C uses the manufacturer number unchanged. One column cannot hold both meanings, and in practice it ends up holding whichever one the first buyer typed.

The bill arrives when you try to answer who else sells this exact part. With the MPN in its own column that is a GROUP BY ManufacturerPartNo. Without it, it is a person with three PDF catalogues and an afternoon. It is also the column that makes duplicate detection across distributors possible at all, which is the same reason the cross-reference table earns its keep.

Preferred is a bit, with no dates

IsPreferred BIT is the field everyone adds first and nobody can maintain. There is no history in it, so changing the preferred source overwrites the fact that the old one ever was. Ranking is impossible: “second source, approved, use if the primary runs short” has nowhere to go. Nor is there any validity, which means a sourcing decision that ran to 31 December is still in force on 2 January and nothing in the data says otherwise.

A rank plus a validity window fixes all three. It costs you a date predicate in every query that reads the table, which is the sort of trade worth making once rather than arguing about annually.

The record turns into a price list

Somebody adds UnitPrice. Then PriceBreak1Qty and PriceBreak1Price, and by the third pair of columns the table has become a pricing engine with a bad schema.

Prices are scoped by time, by quantity, by currency and usually by contract. That is a different table and often a different system. Hold the relationship here, and point at the agreement that holds the money. The line is not always obvious, and the honest version is that a single negotiated price with no breaks and no end date will sit in this record in plenty of real implementations without doing harm. It is the second price that starts the trouble.

The unit of measure trap

Your item master says the base unit is EA. The supplier quotes in CS. Ordinary enough, and the item's own conversion handles it.

Then the second supplier ships the same part in cases of 24 while the first ships cases of 12. The conversion has stopped being a property of the item. It is a property of this pairing, and it has to live here, which is why QtyPerOrderUom is in the table above.

We have argued elsewhere that conversions belong to the item, and for standard units they do: a metre is a hundred centimetres regardless of who invoices you. Supplier-specific pack sizes are the exception, and a model that does not allow for it produces purchase orders wrong by exactly a factor of two, in the same direction, until somebody counts a pallet.

Who owns it

This record has an ownership problem that the two masters either side of it do not. The facts in it belong to procurement. The identifiers in it belong to master data. And procurement does not experience any of it as data maintenance.

A buyer negotiates a shorter lead time and types 10 over 14, because typing it into the ERP is how the number becomes real. No review, no record of who changed it or what it was before. Six months on, planning is running MRP against a lead time that one person changed for one order during one bad week, and the person who could explain it has moved to a different category.

Two controls cover most of that. Put sourcing rank and lead time behind an approval step, so a change is proposed and reviewed rather than typed. And keep field-level history, so “the lead time used to be 14 days” is a query rather than an argument.

Building this without writing the DDL

The SQL above is the right form if you are extending a database you own. In a master data platform you express the same thing as an entity: one SupplierItem entity with two domain attributes, one pointing at Supplier and one at Item, and the remaining columns as ordinary attributes. Derived columns then pull the supplier name and the item description through the domain hop, so a buyer reads a grid that names both sides without either value living in two places.

One limitation to know about before you start. Primentra enforces uniqueness on the entity Code rather than across a combination of attributes, so the composite key has to live in the Code itself, as ACME-4471, rather than in a constraint spanning the two domain columns. Duplicate codes are rejected on save, which gets you the same protection for the same pairing, but if you need the database itself to reject a duplicate pair independently of the code, that remains a unique index in SQL.

What comes with it is the part that is awkward to write by hand: approval on the fields that need it, and a field-level audit trail on all of them. Both matter more here than on the masters either side, because this is the record a buyer edits under time pressure.

Common questions

If you are mapping purchasing info records out of SAP or MDS and want a second opinion on the target model, do get in touch. Happy to share what we have seen go wrong, including the plant-scoping problem that costs people a week.