E-commerce and retail fundamentals

Four words candidates use interchangeably, and the schema is where the confusion becomes expensive.

product      the thing a customer shops for. Marketing lives here.
  product_id, title, description, brand, category_id, status

variant      one buyable configuration of that product. Options live here.
  variant_id, product_id, option_values {colour: navy, size: M},
  barcode, weight_grams, status

sku          the stock-keeping unit. Inventory is counted against this.
  sku_id, variant_id, pack_size, supplier_ref

listing      the offer: this SKU, on this channel, by this seller, at this price.
  listing_id, sku_id, channel_id, seller_id, price_minor, currency,
  valid_from, valid_to, listing_status

The conflation that hurts, seen constantly:

  product(id, title, colour, size, price, stock_count)

The flat table cannot express a t-shirt. Navy medium and navy large are separate things to count, ship and photograph, but one thing to describe and rank, and a single row forces you to choose which. Teams that choose product duplicate the description eight times and diverge; teams that choose variant lose the concept a customer actually searches for.

Separating SKU from variant looks like over-modelling until a supplier ships the same navy medium as a single and as a pack of three, or until two variants share one physical item under different barcodes. Inventory is counted against the thing on the shelf, which is the SKU, and price is quoted against the thing on offer, which is the listing.

The listing is the entity most people omit, and it is the one that decides whether you can ever become a marketplace or sell on a second channel. Price, availability and even title vary by channel and by seller, so they belong on an offer row and not on the product. A price column on the product means every subsequent requirement — a different price in Ireland, a marketplace seller undercutting you, a wholesale tier — becomes a schema migration.

The effective dating on the listing is what makes a price change auditable. A mutated price column answers "what does this cost" and nothing else, and the question you will actually be asked is what it cost on the fourteenth of March, by a customer holding a screenshot.

Getting complete, correct, consistent attributes for hundreds of thousands of products from suppliers who each supply a different subset in a different format. The hard part is not storage, it is workflow: a product arrives with a title and a price, and cannot be published until it has images, dimensions, a category, a material and whatever the destination channel demands. So a PIM is an enrichment pipeline with per-attribute ownership, completeness scoring against a channel's requirements, and a review state — not a table of columns. The consequence engineers miss is that publication is a gate rather than a flag, and a product blocked on a missing hazardous-goods field is a merchandising problem with a queue behind it.

As data, not as columns, because the attribute set differs per category and changes weekly. A television needs a screen size and a panel type, a wine needs a vintage and a region, and adding a category must not require a migration. So you carry an attribute definition table — name, data type, unit, allowed values, which categories require it — and store values against it. The cost is that typed validation moves into your code rather than the database, and that querying becomes awkward, which is why the search index rather than the source table serves faceted browse. The taxonomy itself needs to be a graph rather than a tree, since a product legitimately belongs under both "gifts for her" and "handbags", and a strict tree forces duplicates.

They are large, immutable, numerous and required in many renditions, so they belong in an object store with a CDN in front and never in the database. Each image needs a defined role — primary, alternate, swatch, lifestyle — because the front end must pick deterministically rather than take whatever is first. The processing is a pipeline: ingest, validate dimensions and colour space, generate renditions, and publish. Two details cause real incidents. Assets are versioned by URL rather than overwritten, because overwriting means a stale CDN copy shows the old jacket for hours. And asset readiness gates publication, since a product page with a missing hero image converts worse than no product page.

Because every browse, search, category page and recommendation reads it while merchandisers write it a few thousand times a day, giving read-to-write ratios in the thousands. That asymmetry means the write model and the read model should not be the same shape. The source of truth is normalised and validated for correctness; the read path is a denormalised document per product containing everything a page needs, published to a cache, a CDN and a search index. The consequence is a projection you must be able to rebuild and must monitor for staleness, because the failure mode is not an error but a product page showing last week's price. Publication therefore becomes an event with a lag metric somebody owns.

Because attributes are what search and browse run on. A product with no colour attribute cannot be filtered by colour, so it is invisible to the customer who filters — which is most of them — and a product with the wrong category is invisible everywhere. Missing dimensions mean shipping is estimated wrongly and either loses margin or loses the sale at the shipping step. Wrong or absent attributes also drive returns, since "not as described" is a return reason generated by your own data. This is why completeness is tracked per channel as a metric with a target, and why enrichment gets funded: it is one of the few engineering-adjacent programmes with a directly attributable conversion effect.

Everything quietly. The PIM enriches a title, the ERP overwrites it from the supplier feed on the nightly load, and each system is behaving correctly. The symptom is a field that reverts, reported as a flaky UI, and the cause is undefined ownership at attribute granularity rather than at record granularity — merchandising owns the title and the imagery, finance owns the cost, the supplier owns the barcode and the pack size. So the fix is an ownership map and a rule that a non-owning system may read but never write, enforced at the integration rather than by convention. Without it, every feed is a race and the last writer of the night wins.