A merchandiser asks how many products you have. The answer is either four hundred or eleven thousand, depending on who is counting, and both people are confident. That is not a reporting problem. It is the model showing through.
Short answer: a SKU is a unit you sell. A product is a thing you describe. A variant is a product that differs from its siblings in a small, enumerated set of axes. Model these as three distinct levels, put each attribute at the highest level where it is still true, and the maintenance problem largely disappears.
The three levels
Family. Defines which attributes exist. All shirts have a fabric composition, a collar type and a fit. A family is a schema, not a thing you sell, and most catalogues need surprisingly few of them.
Product. Carries the values shared by everything beneath it. The fabric composition of the Oxford shirt. Its care instructions, its brand, its story. This is the level a customer thinks they are browsing.
Variant. Carries only what differs, along a small number of declared axes. Colour and size, usually. Sometimes a third, and if you find yourself needing a fourth it is worth checking whether one of them is really a separate product.
The SKU is the identifier of the sellable unit, which is normally the variant. It is an inventory concept that has been borrowed by everyone else, which is why the same word means different things in a warehouse conversation and a merchandising one.
Put each attribute at the highest level where it is still true
That single rule does most of the work.
Fabric composition is true of every variant of the shirt, so it sits on the product. Colour is not, so it sits on the variant. Weight is interesting: usually true at product level, occasionally variant level if sizes differ enough to matter for shipping, and if you are not sure, the safe answer is variant with a default inherited from the product.
The failure mode when you get this wrong in the “too low” direction is duplication: the fabric composition stored thirty times, edited in twenty-nine places, wrong in one. The failure mode in the “too high” direction is a lie: a single weight value that is wrong for the extra large.
Duplication is the more expensive of the two, because it degrades silently. A wrong value announces itself eventually. Thirty copies of a value that were all correct at the time just drift.
The combinatorics, which nobody models for
Six colours times five sizes is thirty variants. Add a fit and it is ninety. Add a market with its own copy and it is ninety times the number of markets in the fields that are localisable.
That number is fine when it is generated and enforced. It is unmanageable when it is entered.
Two consequences follow. First, variant creation should be generated from the declared axes, not typed, so that the thirty exist because six times five is thirty and not because someone made thirty rows. Second, the axes have to be enumerated in the model, so that “which colours does this come in” is a query rather than an inspection.
There is also the case of the incomplete grid, which is normal and worth modelling deliberately. Not every colour comes in every size. A generated grid with explicit exclusions is maintainable. A hand-built list of the twenty-three combinations that exist is not, because nobody will remember why the other seven are missing.
Identifiers, and why you need more than one
The catalogue needs at least three kinds of identifier and they are not interchangeable.
Your internal identifier. Stable, meaningless, and never reused. Meaningless matters: an identifier that encodes the category will be wrong the first time a product is recategorised, and you will not be allowed to change it because it is printed on something.
The external trade identifier. GTIN, EAN or UPC, issued under the GS1 system. Marketplaces increasingly reject listings without a valid one, and validity means the check digit computes and the prefix is actually yours. Borrowed or invented barcodes fail at the worst moment.
The manufacturer’s identifier. MPN, if you resell. It is the only reliable way to reconcile your record with a supplier’s, and it is the field that makes data synchronisation with trading partners possible at all.
Keep them in separate fields. The pattern of jamming several identifiers into one string with a separator survives until the first supplier whose identifier contains that separator.
Where do images fit in the variant structure?
At every level, and this is the part that catches people.
Some images are true of the family, such as a size chart. Some are true of the product, such as a detail shot of the fabric. Most are true of the variant, because the blue one and the red one look different. A few are true of a combination that is not a variant axis, such as a lifestyle shot showing three colours together.
The model that works is an association with a role, held on the product side: this asset, in this role, for this product or variant. Roles are a controlled list. Primary, alternate, detail, lifestyle, size chart, packaging. The channel mapping then says “the marketplace wants the primary plus up to six alternates”, and that is a query rather than a folder convention.
What the model should not do is put the association in the asset system, for the reasons in DAM vs PIM, and it should not store crops per variant, because renditions are derived rather than stored.
When is flattening variants actually correct?
Twice, and it is worth naming them because the general advice above is otherwise absolute.
When variants are genuinely different products. If the blue one and the red one have different specifications, different regulatory status, different suppliers and different marketing, they are two products that happen to look similar. Forcing them into a variant grid produces a parent record with almost nothing on it.
When your only channel demands it. Some marketplaces model everything as a flat list of sellable items with no parent concept. That is a channel mapping problem, not a modelling problem, and the correct response is to keep the structured model and flatten at export. The exception is if you have exactly one channel, forever, and no site of your own, in which case matching its model is defensible and you should write down that you did it on purpose.
Everything else that looks like a reason to flatten is usually a tool that cannot express hierarchy, which is a reason to check the tool. The questions to ask about that are in what to demand from a PIM API before you buy, and the export-side consequences are in syndicating product data to marketplaces.
The wider set of modelling decisions this one sits inside is in the product data model that survives growth, and if you are still working out whether a dedicated system is warranted at all, start with what a PIM actually does.