The product master data model: three levels your item master probably collapsed into one

One jacket, five sizes, eight colours. Your item master holds forty rows, and every one of them carries the same marketing description, the same brand, the same country of origin, and the same care instructions. When the copywriter improves that description, someone updates forty rows. Thirty-six of them, usually. The webshop then shows one text for the navy medium and a slightly older one for the red small, and whoever notices files it as a data quality issue.

It is not a data quality issue in any useful sense, because nothing was typed wrong. The table has one level and the business has three: the product that marketing names, the variant that the warehouse counts, and the pack that leaves on a lorry. Collapse those into one row and every attribute has to pick a level it does not belong to. I wrote recently about the same failure in supplier data, where it shows up as four records for one company. In product data it shows up as one record repeated forty times, which is the same mistake seen from the other end.

What a product master data model actually is

A product master data model is the entity design that separates what a product is from what you stock and what you ship. Three levels: the base product carrying name, brand, description, and category; the variant or SKU carrying size, colour, and stock unit; and the packaging level carrying the GTIN and the quantity per case. Attributes attach to whichever level they are actually true for.

Most item masters have the middle level only, because the middle level is what ERP needs. ERP counts stock, prices lines, and picks orders, and all three of those happen at SKU granularity. The level above and the level below were never missing from the business, only from the table, so they went somewhere else: the product level into the webshop or the PIM, the packaging level into a logistics spreadsheet. That is why product data is the domain where nobody can agree who owns it.

Which level does this attribute belong to

This is the whole design, and it takes one question per attribute. Ask it before you add a column anywhere.

Weight is the attribute that catches people, because it exists at two levels and means different things. Net weight is a property of the variant. Gross weight is a property of the pack, and it includes the cardboard. Collapse them into one field and the freight calculation is wrong by the weight of the packaging on every shipment, always in the same direction, which is the kind of error that survives for years because it never looks random.

The SQL

The product table stays deliberately small. It holds what is true of the product regardless of which one you pick off the shelf, and nothing else.

CREATE TABLE dbo.Product (
    ProductId       INT IDENTITY(1,1) NOT NULL,
    ProductNumber   VARCHAR(20)   NOT NULL,   -- stable, never reused
    Name            NVARCHAR(200) NOT NULL,
    BrandId         INT           NULL,
    CategoryId      INT           NOT NULL,   -- decides which attributes apply
    LifecycleStatus VARCHAR(20)   NOT NULL,   -- draft|active|phase_out|archived
    CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductId),
    CONSTRAINT UQ_Product_Number UNIQUE (ProductNumber),
    CONSTRAINT FK_Product_Category FOREIGN KEY (CategoryId)
        REFERENCES dbo.ProductCategory (CategoryId)
);

CREATE TABLE dbo.ProductVariant (
    VariantId    INT IDENTITY(1,1) NOT NULL,
    ProductId    INT           NOT NULL,
    Sku          VARCHAR(40)   NOT NULL,
    StockUomCode VARCHAR(10)   NOT NULL,   -- what the warehouse counts in
    NetWeightKg  DECIMAL(12,4) NULL,       -- the thing, without packaging
    IsActive     BIT           NOT NULL CONSTRAINT DF_Variant_Active DEFAULT 1,
    CONSTRAINT PK_ProductVariant PRIMARY KEY CLUSTERED (VariantId),
    CONSTRAINT UQ_ProductVariant_Sku UNIQUE (Sku),
    CONSTRAINT FK_ProductVariant_Product FOREIGN KEY (ProductId)
        REFERENCES dbo.Product (ProductId)
);

Packaging is the level people skip, and skipping it is what forces a single GTIN field onto the item. A variant sold as a single, in an inner of six, and in a case of twenty-four has three barcodes, three sets of dimensions, and one conversion factor each. That is a table.

CREATE TABLE dbo.ProductPackaging (
    PackagingId    INT IDENTITY(1,1) NOT NULL,
    VariantId      INT           NOT NULL,
    PackagingLevel VARCHAR(20)   NOT NULL,   -- each|inner|case|pallet
    Gtin           CHAR(14)      NULL,       -- GTIN-14, left-padded
    QtyOfStockUom  DECIMAL(18,4) NOT NULL,   -- how many stock units in here
    GrossWeightKg  DECIMAL(12,4) NULL,       -- the thing plus the cardboard
    IsOrderUnit    BIT           NOT NULL CONSTRAINT DF_Pack_Order DEFAULT 0,
    CONSTRAINT PK_ProductPackaging PRIMARY KEY CLUSTERED (PackagingId),
    CONSTRAINT UQ_ProductPackaging_Level UNIQUE (VariantId, PackagingLevel),
    CONSTRAINT FK_ProductPackaging_Variant FOREIGN KEY (VariantId)
        REFERENCES dbo.ProductVariant (VariantId),
    CONSTRAINT CK_ProductPackaging_Qty CHECK (QtyOfStockUom > 0)
);

-- A GTIN identifies one pack in the world. Nobody else may hold it.
CREATE UNIQUE INDEX UX_ProductPackaging_Gtin
    ON dbo.ProductPackaging (Gtin) WHERE Gtin IS NOT NULL;

That unique index is worth more than it looks. A GTIN reused across two packs is the barcode error that shows up at the till or the receiving scanner, and it is far easier to prevent than to unpick once six months of transactions reference both. The QtyOfStockUom column is the other one to get right: it is the same conversion factor that, left at a default of 1, orders twenty-four times what you meant.

Attributes that only some products have

Here is where product diverges from every other master data domain. Suppliers all have a tax number. Customers all have an address. Products have flow rate, hazard class, thread pitch, sleeve length, and shelf life, and no product has more than a handful of them. Add a column per attribute and the item table reaches four hundred columns, three hundred and eighty of them null on any given row, and each new category needs a schema change and a release.

Define the attributes once, say per category which ones apply, and store the values separately. Adding a category then costs rows instead of an ALTER TABLE.

CREATE TABLE dbo.AttributeDefinition (
    AttributeId INT IDENTITY(1,1) NOT NULL,
    Code        VARCHAR(40)  NOT NULL,   -- flow_rate_lpm, hazard_class
    DataType    VARCHAR(20)  NOT NULL,   -- text|number|date|domain
    UomCode     VARCHAR(10)  NULL,       -- unit the number is expressed in
    DomainId    INT          NULL,       -- allowed values, when DataType=domain
    CONSTRAINT PK_AttributeDefinition PRIMARY KEY CLUSTERED (AttributeId),
    CONSTRAINT UQ_AttributeDefinition_Code UNIQUE (Code)
);

-- Which attributes a category expects, and which it insists on.
CREATE TABLE dbo.CategoryAttribute (
    CategoryId  INT NOT NULL,
    AttributeId INT NOT NULL,
    IsRequired  BIT NOT NULL CONSTRAINT DF_CategoryAttribute_Req DEFAULT 0,
    SortOrder   SMALLINT NOT NULL CONSTRAINT DF_CategoryAttribute_Sort DEFAULT 0,
    CONSTRAINT PK_CategoryAttribute PRIMARY KEY CLUSTERED (CategoryId, AttributeId)
);

CREATE TABLE dbo.ProductAttributeValue (
    ProductId   INT NOT NULL,
    AttributeId INT NOT NULL,
    ValueText   NVARCHAR(400)  NULL,
    ValueNumber DECIMAL(18,6)  NULL,
    ValueDate   DATE           NULL,
    CONSTRAINT PK_ProductAttributeValue PRIMARY KEY CLUSTERED (ProductId, AttributeId)
);

Two warnings, because this pattern is easy to take too far. Keep the universal attributes as real columns on Product. Name, category, and status belong in the table, and moving them into the attribute store makes every query worse and buys nothing. And store numbers in ValueNumber rather than as text, because the moment somebody wants pumps between 40 and 80 litres per minute, a text column turns a range filter into a string comparison and quietly returns the wrong pumps.

Identifiers, and why the SKU is not the key

A product carries at least four identifiers, and they identify different things. The product number names the product. The SKU names the variant. The GTIN names the pack. The supplier part number names what the vendor calls it, which belongs on the supplier-item relationship rather than here at all.

Making the SKU the primary key fails on both counts: it sits at the wrong level, and SKUs get reissued. Somebody retires a line, and two years later the same code comes back on a different product because the numbering scheme encodes the category and the sequence rolled over. Surrogate keys at each level, business identifiers behind unique constraints, external system codes in a cross-reference table. This is the decision you cannot reverse cheaply, and product is where it goes wrong most often. The SKU looks so much like the identifier that questioning it feels pedantic, right up to the afternoon somebody reissues one.

One product, several classification trees

CategoryId on the product table does one specific job: it decides which attributes apply. Keep it single and keep it internal, because an attribute set has to resolve to exactly one answer.

Every other classification is a separate tree with its own membership table. Merchandising groups by how customers shop, procurement wants UNSPSC or eCl@ss, finance wants a reporting hierarchy, and customs wants HS codes. Those are four different opinions about the same product and all four are correct. Force them through one column and three of them end up in a spreadsheet, which is how the biggest spend category becomes Miscellaneous. The same rule applies to descriptions: one row per product per language in a translation table, never a DescriptionNL column next to a DescriptionDE column.

Four things that break in practice

All four have the same root, and it is worth naming plainly: an attribute was stored at a level where it is not uniquely true. Once the three levels exist, most arguments about where a field belongs answer themselves. Expect a few weeks of relitigating the ones that were settled the wrong way years ago, though, because somebody built a report on the old placement and that report is on a board pack.

Building this without the schema migrations

The tables above are the right form when you are extending a database you own. The catch is the attribute layer: every new category means new rows in AttributeDefinition and CategoryAttribute, plus a screen to maintain them and validation that a required attribute is actually filled before a product goes live. That is a small application, and it is the part teams underestimate when they decide to build rather than buy.

A master data platform gives you that layer directly. In Primentra the three levels are three entities in one Product model, related through domain attributes, and the category-specific attribute sets are attributes on those entities rather than columns you migrate. Units, currencies, brands, and classification codes become reference entities, so they are picked from a list instead of typed. If you are migrating off Microsoft MDS, the migration is the cheapest moment to split the levels, because moving a flat item entity across as-is carries the forty-row jacket into the new system along with everything else.

Common questions