When 'add sizes to a product' is secretly a data-model decision

The request could not have sounded smaller. "Each product needs a couple of pricing variants: two tub sizes, or a 200g bag and a 1kg bag." Add a dropdown, basically. An afternoon's work.

I nearly built it as an afternoon's work. Then I looked at one line of an existing query and realised it was a schema decision wearing a dropdown's clothes.

The line that changed the answer

The production plan, the thing that tells a grower what to make this week, aggregates demand like this:

group by catalog_item_id

It counts how much of each catalogue item is on order and sets that against the growing pipeline. Simple, and load-bearing.

Now play out the obvious implementation of "add sizes". If each size is its own product row, then a small tub and a large tub are two different catalog_item_ids. The production plan would count them as two separate crops. It would tell the grower to sow trays for "small tub" and, separately, trays for "large tub", double-counting the exact same plants.

The feature that sounded like a dropdown would have quietly corrupted the number the whole product exists to produce.

The model that came out of it

So the split had to go the other way. catalog_items stays the crop: the thing you actually grow, the production unit. The sellable size and its price move into a new variant layer underneath. One crop, many variants. The crop is what you make; the variant is what you sell.

That keeps group by catalog_item_id honest. Demand still aggregates per crop, because every tub size of the same microgreen points back to one crop. The plan counts trays once.

I shipped this as the schema layer first (catalog_variants under catalog_items), with a default variant backfilled for every existing product so nothing downstream broke: 12 default "Each" variants, existing prices and order lines re-pointed at them. Additive and idempotent, no read path changed on the day.

What I didn't do

• Each size as its own product row. The double-counting above. Rejected outright.
• A second text field on catalog_items. It's single-valued, so it can't hold two prices, and the variant field is already spoken for by the cultivar name. A dead end twice over.
• Building it before writing it down. This one's the interesting rejection. I wrote an ADR and a plan first, because the change ripples into production planning through a size-to-tray yield conversion (how many trays does a 1kg bag actually need?) that you cannot guess your way through at the keyboard. When a change touches the number people plan their week around, the thinking is the work.

The tell

The lesson I'm keeping: the requirement sounded like UI and was actually a crop-versus-SKU modelling decision hiding underneath. The tell was a single group by. If I'd gone straight to the dropdown, everything would have looked fine on screen and the production plan would have started lying.

The reading is fun. The doing is the point. But sometimes the ten minutes reading one old query is what stops the doing from being wrong.

More to come as I build this in the open. What's the smallest-sounding request that turned into a schema change on you?