Product Variants

The problem, and the version everyone writes first

The obvious design is a variants table with a colour column and a size column. It works. It keeps working right up until somebody adds a laptop, where the axes are memory and storage, and then you need a schema migration to sell a product.

The axes are data, not columns. Once you accept that, the shape below is what you get, and it is the shape every catalogue converges on eventually.

The five tables

Two of them are shared by the whole shop, and three belong to the resource that offers variants. That split is the first design decision and the one everything else follows from.

• options — one axis of choice. Name, a kind telling a storefront how to draw it, and affects_price.
• option_values — one choice on that axis. Label, a swatch colour, a swatch image, and a signed price_delta.
• <resource>_options — which axes this product offers, and in what order. Without it, every product would offer every option in the shop and a t-shirt would ask for a memory size.
• <resource>_variants — one buyable combination. SKU, stock, an optional price override, images, active.
• variant_option_values — the join that says which values define each combination.

Three decisions, and the worse alternative to each

Each of these has an obvious alternative that looks simpler and costs you later. They are the reason the schema is worth reading rather than just installing.

Options are shop-wide, not per product

Colour is Colour whether it is on a shirt or a phone case. Give each product its own colours and within a month you have four spellings of it, a filter that can only match one of them, and no way to tell which rows belong together. That is why options and option_values sit outside the resource, and why running grit add variants a second time for another resource adds only that resource's three tables.

affects_price lives on the option, not the value

"Does memory change the price" is a fact about memory, not about 32GB. Put the flag on each value instead and you have made it possible to say that 32GB is price-affecting while 16GB is not, which is not a thing anyone means, and which you then have to defend against every time you resolve a price.

The practical effect: a value's price_delta is ignored entirely while its option says the axis does not affect price. A stray number typed on one swatch cannot charge a customer extra for red.

Stock and images live on the variant, not the value

Red/XXL selling out while Blue/XXL is still in stock is the normal case, not the edge case. And the photograph of the red one is a photograph of a combination, so it belongs to the combination.

A value does carry a picture, but that one is the swatch: a small square of the colour, doing a different job. Both exist, deliberately.

The price is resolved, never stored

This is the part to read twice.

Storing the resolved number is the tempting version, and it is a bug with a delay on it. Store it, change the product's price six months later, and every variant quietly keeps the old figure. Nothing errors. The listing page and the receipt simply disagree, and you find out from a customer.

A worked example, from a seeded shop. The product costs 354.48. Colour declares affects_price: false; Size declares true, and XL carries a delta of 2.50:

The override is the escape hatch for a combination somebody priced by hand, and it wins outright when set. Clearing it falls back to the resolved figure, which is why the admin shows that figure as the override box's placeholder: clearing it is never a guess about what the price becomes.

One resolver answers this for every surface. The admin table, the public payload and the checkout re-price all call the same ResolvePrice, so three callers cannot arrive at three prices.

Generating the matrix

The combinations are the cartesian product of the product's axes, built iteratively rather than recursively, because the number of axes is data and a recursive version needs a depth nobody declared.

Three properties are worth knowing:

• It is additive. Existing rows are left exactly as they are: their SKU, stock, price and photographs are somebody's work. Adding a fifth colour and pressing generate adds four rows rather than resetting sixteen.
• It is idempotent. Combinations are fingerprinted by their value ids, order-independently, so running it twice adds nothing the second time.
• It refuses past a cap of 200. Four options with five values each is 625 rows, and a button that silently writes those has destroyed the page it was meant to help with.

Changing which options a product offers clears its matrix. It has to: a variant is defined by the axes the product offered when it was generated, so dropping Size leaves rows meaning "Red, and something", which is not a thing anyone can buy or ship. The admin says how many rows that will cost before it does it, and saving the same set again does nothing at all.

Installing it

The seed is deliberate rather than faked: Size affects price and XL costs 2.50 more, Colour does not, and one combination in seven is out of stock. That last one is on purpose. The disabled swatch is most of the work on a product page and the easiest state to forget to build, so the seed puts it on screen unasked.

It is its own command rather than a flag on generate resource precisely because two of the five tables are shared. Run it again for a second resource and only that resource's tables are added.

In the admin

Options is a sidebar entry, because the table is shop-wide. An option carries a name, a kind (swatch, size or select) and the price flag; its values carry a label, a swatch colour and a delta. A value can be deleted only while nothing is built on it, and the server says so rather than cascading.

The matrix is on the product's own detail page, because a variant is a fact about one product and that is where you go looking for it. Choose the axes, generate, then edit SKU, stock, price and active state inline. Edits collect into one Save, and a value typed back to what it already was is not a change, so a save never bumps the version of every row you clicked into.

The columns are yours to extend. A variant stores its own photographs, so showing them is a column you add:

A cell renderer is handed the variant, its unsaved draft, a patch that feeds the same Save button the built-in cells feed, and the resolved price. So a column of your own is editable without becoming a second way to write.

The endpoints

Behind auth, for the admin:

And one public endpoint, in the API-key-guarded group with the rest of the catalogue, for a storefront that has no logged-in user:

One request rather than three, because a picker needs the options to draw, the combinations to match a selection against, and the range for a "from" price. Fetching those separately means a round trip every time somebody clicks a swatch.

Three things about that payload are deliberate:

• Stock is a boolean. in_stock, never the count. It is what the page renders, and the number is a business fact your competitors would enjoy.
• Inactive combinations are absent, not greyed out. A variant somebody switched off is not something the shop sells, and publishing it invites a client to render a choice that can never be completed.
• price_delta is zeroed unless the option affects price, so a picker cannot label a swatch "+ 20" and then resolve to the base price.

A product with no variants gets empty lists and a range of its own price, which is what lets a storefront render one component either way.

option_value_ids rather than nested values, because the values are already in the options list and a picker matches a selection by comparing ids. Nesting them would send the same objects twice. The storefront guide builds the picker against this payload in Step 4f, including the two matching functions that each have a wrong version that sells the wrong thing.

What ships with it

Six tests are written into your project rather than kept in the framework, so they run against your own database dialect. They cover the cases where a mistake is silent: a price resolved from an axis that should not affect it, an override that should win, a base price change that must reach every variant, a combination matched by exactly its values rather than a subset, a generator that must be idempotent and capped, and a price range that must skip out-of-stock rows.

That last one matters more than it looks: a "from 49" that can only be had by buying something unavailable is a lie the customer discovers at the last step.

What it does not do yet

• Filtering the public list by a variant value. ?colour=black on /public/products is not wired: public filters are built from the product's own published columns, and a filter reaching through the variant join is a different query.
• Bulk edit across the matrix. You can edit every row and save them in one request, but there is no "set every XL to 40".

Both are on the roadmap rather than in the box, and it is better to know that before you promise a filter to somebody.