Modelling Garment Variants Without Breaking the Till

Retail catalogues look simple until you have a jacket that comes in 6 colours, 5 sizes, and 3 fits. That's 90 SKUs from one product. Get the data model wrong and your POS, your warehouse, and your e-commerce front end start telling three different stories.

Why Variant Modelling Is Harder Than It Looks

A flat product table works fine for a catalogue of 200 distinct items. The moment you introduce attributes that combine, it falls apart. The naive approach is to pre-enumerate every combination as its own row. For small catalogues this is just ugly. For a mid-size apparel brand with 400 base styles, each with colour and size options, you can easily land north of 15,000 SKU rows before the season is half over.

The real problem is not storage. It's consistency. When a product's name changes, you update one record and miss 40 SKU rows. When a colour is retired mid-season, you need a bulk operation that your team inevitably runs with a typo in the WHERE clause. The schema fights you at every step.

There are two general patterns worth knowing before you build anything.

The EAV Trap

Entity-Attribute-Value tables have been solving the "arbitrary attributes" problem since the 1990s. The idea is simple: instead of a column per attribute, you store rows like (product_id, attribute_name, attribute_value). Magento used this pattern extensively. It scales terribly for reads, since a query that would touch one row in a flat schema now requires several joins or pivots, and it makes validation nearly impossible at the database layer.

If you are on PostgreSQL, avoid EAV. You have better options.

The Relational Variant Model

The structure that actually holds up in production looks like this:

Table	Purpose
products	One row per base style. Holds brand, category, description.
attributes	Enumerated types: colour, size, fit, material.
attribute_values	Valid values per attribute: Red, Blue; XS, S, M, L, XL.
product_variants	One row per valid combination. References product + N attribute values.
variant_skus	Maps a variant to a retailer-facing SKU string.
inventory_records	Stock levels per variant per location.

The junction between product_variants and attribute_values is a many-to-many table. Each variant row references as many attribute values as the product type requires. A plain t-shirt might reference only colour and size. A boot might also reference width and lining.

This means your POS query for "give me everything in the small red version of style X" is a straightforward join with indexed foreign keys, not a string-parsing operation on a JSON blob.

How Do You Handle Attributes That Differ by Product Type?

This is where most teams reach for JSON columns. PostgreSQL's jsonb type is genuinely useful here, but it needs to be used precisely. Store attributes that are truly unstructured and query-irrelevant in jsonb. Do not store attributes you filter or sort on in jsonb, because you lose index performance the moment you go beyond simple GIN indexes on top-level keys.

A workable split:

• Structured and queried: size, colour, fit. These go in attribute_values with foreign key constraints.
• Structured but rarely filtered: care instructions, country of manufacture. These can live in jsonb on the product row.
• Completely unstructured: marketing copy variations, seasonal tags. Fine in jsonb or a document store entirely separate from the transactional schema.

The practical rule: if your warehouse team filters by it, it needs a proper column or FK. If only the CMS touches it, jsonb is acceptable.

What Breaks at the Till When the Model Is Wrong?

POS systems are unforgiving. A sale transaction needs to resolve a SKU to a variant, deduct inventory, and apply any pricing rule, in one database round-trip if you want sub-100ms response times under load. Three failure modes come up repeatedly.

Pricing inconsistencies. If pricing lives on the SKU string rather than on the variant record, a SKU rename breaks pricing lookups silently. The fix is to store price overrides on product_variants with a fallback to the base product price. Promotions and tiered pricing should reference product_variant_id, not the SKU string.

Inventory phantom stock. If inventory is tracked per product rather than per variant per location, you get overselling. A warehouse with 10 units of "medium blue" and 0 units of "large red" looks like it has 10 units of the style if you aggregate carelessly. Every inventory deduction must hit inventory_records with the variant and location composite key.

Variant proliferation lag. When a buyer adds a new colourway two weeks before launch, the system needs to create new attribute_values rows, new product_variants rows, and propagate pricing rules, without touching unrelated variants. If your model conflates these concerns, the data team ends up doing manual surgery on live tables under time pressure.

Syncing Variants Across POS, Warehouse, and E-Commerce

The catalogue is one schema. The problem is that a POS, a WMS, and a Shopify storefront each have their own identifiers. Mapping tables are unavoidable. What you want to avoid is treating any external ID as a primary key internally.

Keep a channel_variant_mappings table: (internal_variant_id, channel, external_id). The Shopify variant ID goes here. The WMS barcode goes here. Your internal product_variant_id is the source of truth. When Shopify creates a new variant ID after a product republish, you update one row in channel_variant_mappings and nothing else needs to change.

Event-driven sync matters here. Rather than polling the database on a schedule, publish a variant.updated event whenever a variant's stock, price, or status changes. Consumers (the POS adapter, the WMS adapter, the e-commerce adapter) process these independently. This decouples schema migrations on one side from the other two. It also makes the audit trail explicit: you can replay events to reconstruct the state of any variant at any point in time.

If you are running Kafka, a partition key of product_id keeps variant events for the same product in order without over-partitioning.

Conclusion

The data model you build for garment variants in month one will still be running five seasons later, so the choices compound. Use a proper relational variant model with attribute_values and junction tables. Keep structured filterable data out of jsonb. Price and track inventory against product_variant_id, not SKU strings. Map external channel IDs in a dedicated table. Publish events rather than polling.

If you are starting a replatform or a new build, map out your attribute matrix before touching the schema. The number of variant combinations your most complex product type produces will tell you immediately whether you need a flat model or a full relational one.

---

FAQ

Why not just use Shopify's built-in variant model and call it done?

Shopify caps a product at 100 variants and 3 option types. For most DTC brands that is fine. For wholesale or multi-channel operations with wide-and-deep size runs, you hit the ceiling quickly. A custom schema gives you unlimited dimensions and lets you own the pricing and inventory logic rather than working around platform constraints.

Is PostgreSQL jsonb appropriate for storing size and colour?

Not if you filter by them at query time. jsonb GIN indexes work for existence checks and simple key-value lookups, but range queries and joins across jsonb fields are expensive compared to indexed foreign keys. Use jsonb for attributes that are read-only from the application's perspective.

How should pricing work for a variant that is on promotion?

Store a price_overrides table keyed to (product_variant_id, price_list_id, valid_from, valid_to). At query time, select the most specific active override, falling back to the base product price. This handles seasonal sales, wholesale tiers, and loyalty pricing without changing the underlying variant record.

What's the right way to retire a colour mid-season without breaking historical orders?

Soft-delete the attribute_value row by setting an active flag to false. Never delete it. Historical order lines, returns, and reports reference that variant ID. Deactivating it removes it from new-order flows while keeping the data intact. Cascade-deleting would corrupt your sales history.

When does this relational approach become the wrong choice?

If your catalogue is static, small (under 500 SKUs), and single-channel, this model is over-engineered. A flat product table with a SKU column and a JSON attributes column will run fine and take a day to build. The relational variant model pays for itself at scale, under multi-channel sync pressure, or when pricing and inventory rules become complex.

Have a project in mind? Contact Sodio Technologies to discuss your requirements and explore the right technology solution for your business.