Snapshots vs References
The price the customer paid lives on OrderItem, not on Product. Whenever a fact was true at a moment and the thing it refers to can change, the fact is a snapshot; a reference is right only when the current value is the one wanted.
The situation, the reflex, and why it stalls
Every lesson starts where being stuck starts: someone has a problem, and the first move that comes to mind feels like progress.
When one entity refers to another, should it hold a reference to the current thing or a copy of the thing as it was — and how do I tell which?
An admin changed a product's price on Monday. On Tuesday, every order placed last week shows the new price, the totals no longer match what was charged, and a customer has emailed to ask why their receipt changed. The orders reference the product, which seemed obviously correct.
Reference everything, once, by id. It is what normalisation seems to say — do not duplicate data — and it means a price change is one update. Copying the price onto the order line feels like the mistake a beginner makes.
The order now shows the present instead of the past. "What did this customer pay?" is answered with "whatever the product costs today", which is a different question. The database is consistent and wrong.
- The order now shows the present instead of the past. "What did this customer pay?" is answered with "whatever the product costs today", which is a different question. The database is consistent and wrong.
- Fixing it after the fact is impossible: the old prices were overwritten, and last week's totals can only be reconstructed from the payment provider's records — a fact that lived outside the system all along.
- The instinct generalises badly. Product name, customer address, shipping fee, tax rate — every one changes over time, and every reference to a mutable thing quietly reports the present.
The move
Precisely enough to apply it to a problem you have never seen — not a slogan.
- For every relationship, ask which moment does this fact belong to? If the fact is about *now* — "which product is this?", "who is this customer?" — a reference to the current row is right. If the fact is about *then* — "what did they pay?", "where was it shipped?", "what was it called on the receipt?" — the value as it was must be copied at that moment, because the thing it refers to will change and the fact must not.
- Notice that this is not a violation of normalisation; it is a different fact. "The product's price" and "the price this customer paid for this item" are two facts that happened to be equal at checkout. Storing both is not duplication; it is recording two things. Normalisation forbids storing the same fact twice, not two facts that share a value (Normalization: 1NF to BCNF).
- Snapshot at the boundary where the fact becomes historical — checkout, sending, publishing — and reference before it. The cart references products, because a cart is about now and should show the current price; the OrderItem snapshots, because an order is about then. The transition from cart to order is exactly the copy.
- When the history of the referenced thing itself matters — "what was the price at any point last year?" — neither a reference nor a snapshot on the order is enough; the referenced entity needs its own history. That is a bigger decision, and the why ladder below is how to tell whether it is needed (Overwrite or Append?).
Two order lines that look the same
The two schemas below both "have an OrderItem". One answers "which product" and reports the present; the other answers "what did they pay" and keeps the past. The engineering reason is not tidiness; it is that only one of them can ever produce a correct receipt.
order_items(order_id, product_id, quantity). Total computed by joining to products.price at read time.
order_items(order_id, product_id, quantity, price_paid, name_at_purchase). Total computed from price_paid; product_id kept for reorders and returns.
"What did this customer pay?" is a fact about the moment of checkout, and the product's price is a fact about now. The first schema stores only the second fact and therefore cannot answer the first after any price change; the second stores both, because they are both facts.
"We need a price history table"
The bug often produces the reflex "version the product". Sometimes that is right; usually the real requirement is smaller, and the ladder is how to find out before building temporal tables.
“We need a full price history table for products.”
- ↓Why do we need price history? Because orders show the wrong price after a change.
- ↓Why do orders need the old price? Because the receipt, the invoice and refunds must show what the customer actually paid.
- ↓Why must that come from the product's history rather than from the order? It need not; the order knew the price at the moment it was placed, and nothing else needs the price at arbitrary dates.
price_paid column on OrderItem, written at checkout in the same transaction that creates the order. No history table, no temporal queries.the claim was right when Someone genuinely needs the price at arbitrary points in time independent of orders — an audit of pricing decisions, "what was the price when the campaign ran?", or a regulator asking for the catalog as it stood on a date. Then the product needs its own history, and the OrderItem snapshot stays as well, because they answer different questions.
The copy, at the boundary, in one place
The snapshot is made exactly once, at checkout, in the same transaction that creates the order — and nowhere else. The pseudocode is the place a later reader will find the decision, which is why it carries the reason as a comment.
1checkout(cart, address, idempotencyKey):2 transaction:3 order = insert Order(customer, placedAt = now,4 shippingAddress = copy(address), # snapshot: where it went5 shippingFee = currentShippingFee) # snapshot: the fee then6 for line in cart.lines:7 product = lock Product(line.productId) # reference: which product8 require product.stock >= line.qty9 product.stock -= line.qty10 insert OrderItem(order, productId = product.id, # reference kept11 qty = line.qty,12 pricePaid = product.price, # snapshot: what they paid13 nameAtPurchase = product.name) # snapshot: what the receipt says14 delete cart.lines # the cart was about "now"; it is over15 return order16 17# Receipt, invoice, returns: read OrderItem.pricePaid — never Product.price.The cart referenced; the order snapshots; the boundary between them is one function. A price change tomorrow touches Product and nothing here.
How to do it
Most important first.
- For each foreign key, write the question the relationship answers and underline "now" or "then" in it.
- For every "then", list the attributes that will be read later and copy exactly those onto the referring row at the moment the fact becomes historical — no more.
- Keep the reference too, where identity still matters: OrderItem holds
product_id*and*price_paid*and*name_at_purchase, because "which product" is still a now-question for reorders and returns. - Find the boundary in code where the copy happens and put the snapshot there — one place, one function — so nobody later "fixes" it by reading the product (Where the Transaction Boundary Goes).
- Check the invoice, the receipt email and the returns screen: each reads the snapshot, never the product, or the same bug returns through a different door.
Worked on a concrete problem
The move has to produce something. This is what it produced.
- The store. OrderItem:
product_id(reference — which product, for reorder and returns),price_paid(snapshot — what they paid),name_at_purchase(snapshot — what the receipt said),quantity. Order:shipping_addressas columns or a JSON blob (snapshot — where it went, even if the customer moves),shipping_fee_charged(snapshot — the fee was a constant that will change). Cart and CartItem: references only, so the cart always shows today's price and today's stock. - The chat app. Message →
sender_idis a reference; the sender's *display name* at the time is not stored, because the product decision is that a renamed user is renamed everywhere. Message *content* is a snapshot by nature — an edited message is a new fact with anedited_at, not an overwrite, if "show edited" is a requirement. - The file-upload service. A share link references the file by id (now — the link should serve the current version) unless the requirement is "the link serves what I shared", in which case the link snapshots a version id. Same relationship, opposite answer, decided by the question the link answers.
How you know it worked
What now exists that did not before, and what question you can now ask.
- Every foreign key has a now-or-then annotation, and the "then" ones carry copied columns beside them.
- A price change on a product changes no existing order, and the test that proves it exists.
- The copy happens in one named place at one boundary, and the receipt, the invoice and the returns screen all read from the snapshot.
- You can say which entities need their own history and which do not, with a reason each.
The questions you can now ask
The field this whole domain exists for. After this lesson, these are the questions to put to an unfamiliar problem.
- ?Which moment does this fact belong to — now, or then?
- ?What attributes of the referenced thing will be read later as they were, and what will be read as they are?
- ?Where is the boundary at which this fact becomes historical, and is the copy made there and only there?
- ?Does anyone need the history of the referenced thing itself, rather than its state at the moments other entities captured it?
What can go wrong
- Snapshot everything. Copying the product's description, image URL and category onto every OrderItem "to be safe" makes each order line a copy of the catalog, and none of it is ever read. Copy what will be read as a then-question.
- Snapshot without the reference.
price_paidandname_at_purchasewith noproduct_idmakes reorders, returns and "how many of this product sold?" impossible; the now-question was still needed. - Snapshot at the wrong boundary. Copying the price into the cart freezes it while the shopper browses, so the cart shows Monday's price on Tuesday and checkout charges a different amount. The cart is about now.
- Snapshots are more columns and a copy step, and every screen that shows an order must know to read them.
- References are one update per change and always current — which is exactly wrong for a receipt and exactly right for a cart.
- Full history on the referenced entity answers every "then" question and costs a versioned table, temporal queries and a great deal of complexity that most stores never need.
- "Snapshots denormalise, so they are a performance hack." They record a different fact. A denormalised copy of the current price would be a hack; the price paid is not a copy of anything.
- "Just version the product table." Versioning answers "what was the price on a date"; it does not answer "what did this customer pay" if the checkout ran during a change, and it is a far larger design than a column on OrderItem.
- "Reference the product and store the total on the order." The total is a derivation of the lines; if the lines reference current prices, the total and the lines disagree after the first change.
Where this applies
Problem-solving advice is stated as universal far more often than it is. These labels say what each method is specific to — and where CONTESTED appears, the note gives the strongest form of the opposing view.
- GENERALNow-or-then applies to every relationship in every domain: an invoice line, a shipping label, a published article's author name, a completed job's configuration.
- DOMAIN-SPECIFICA store snapshots price and address at checkout. A chat app deliberately references the sender so renames propagate. A document system may need full version history because "what did this look like on that date" is the product. The rule is the same; the answers differ by what the domain asks of the past.
- ILLUSTRATIVEThe Monday price change and the customer email are invented; any quantities are for the shape of the argument.
Where the depth lives
This domain asks the question and hands the answer off by name.