Choosing a data model

A record represents something the product needs to distinguish, such as a particular order. Its fields describe that thing. Relationships connect it to other records, while rules state what combinations make sense. A schema expresses parts of this model through tables or documents, fields, keys, and constraints.

The difficult decision in our broken receipt is what “price” means. Today's catalog price and the price accepted yesterday answer different questions. Choosing a database or making the query faster cannot recover a fact we never retained.

For engineering work, a model makes these decisions explicit before different features develop conflicting assumptions. It gives us a way to check whether a change preserves the product's promises. In an interview, a small model makes the same reasoning inspectable: the interviewer can see what you think the system does, question an assumption, and follow your adaptation. In both settings, you should be able to connect each important modeling decision to the behavior it supports.

Our store needs to show today's products, accept purchases containing several items, and display previous receipts. Those are different reads and writes. Opening a product asks, “What is offered now?” Opening a receipt asks, “What did this customer purchase on that occasion?”

We will require each receipt to retain purchased quantities, agreed unit prices, and the product titles shown at checkout. That last requirement is a choice: another product might show current titles on old orders. We need to establish the intended behavior before deciding where the title belongs.

Each order uses one currency. Prices are stored in that currency's minor unit; in our USD examples, 3000 means $30. Checkout must establish that the accepted item prices use the order's currency. Inventory, tax, shipping, and external payment processing are outside this initial model.

Start with Product and Order. An order containing only product IDs can identify the catalog entries involved. But where would we put the fact that this order bought two lamps? Quantity describes the product's participation in a particular purchase. We have found a missing record by tracing an operation, rather than by guessing more nouns.

Product P7 is the lamp. Renaming it should not make old references point to a different product. Order O42 identifies one purchase by customer C9. Buying the same lamp again produces another order, while opening the original receipt refers to O42 again.

Introduce OrderItem for one line within an order. It links the purchase to the product and gives purchase-specific facts a place:

Customer(customer_id)

Product(product_id, title, current_unit_price_minor, currency)

Order(order_id, customer_id, placed_at, currency)

OrderItem(order_id, line_number, product_id,
          quantity, unit_price_minor_at_purchase, title_at_purchase)

A customer can have many orders, while each order belongs to one customer. A placed order contains one or more items. Each item belongs to one order and references one product, which can appear in many purchases. These counts are the relationships' cardinality.

The pair (order_id, line_number) identifies an item. Line 1 of O42 is different from line 1 of O43. We permit the same product on separate lines, so using (order_id, product_id) as the key would impose an unwanted restriction. A separate item ID could also work; it is not required by these operations.

Follow O43 in the rows: it connects to two items, one referencing P7 and another referencing P8. Follow P7: it appears in both orders. The shared product identity does not make those purchase lines the same item. The rows show selected columns; purchased titles and customer records are omitted from this view.

The seller changes P7 from “Desk lamp” at $30 to “Adjustable desk lamp” at $35. Updating the catalog is correct. If O42 retrieves that new price for its two purchased lamps, however, its receipt changes from $60 to $70. Copying catalog updates into old order items causes the same failure.

Keep current title and price on Product. Keep the purchased title, agreed unit price, and quantity on OrderItem. Item (O42, 1) retains quantity 2 and price 3000; its merchandise subtotal stays 6000 cents. The later order O43 can correctly buy P7 at 3500. The subtotal is a derived answer: we can calculate it from retained quantity and price without storing another field in this base model.

These are different facts even when their values initially match. The product's price is current state: it describes the present offer. The purchase price is a snapshot of a selected fact at an agreed moment. It remains fixed because the receipt must explain that purchase. We need not copy the entire product record.

History is a further question: what happened over time? Retaining orders lets us inspect past purchases. It does not automatically give us every price change in the catalog. We would need to capture those changes only if the product required that additional history.

This also explains why quantity belongs to the item rather than the product or order alone. Different orders buy different quantities of the same product; one order can buy different quantities of several products.

For each field, ask: what does this value describe, and which past or present answers should change when it changes? That question is more useful than applying “always normalize” or “always keep history” without a product requirement.

Replay placing O42. Customer C9 accepts two lamps at 3000 cents each in USD. The server must retain the order and its item (O42, 1), including the agreed title, price, and quantity. Reading that purchase afterward should produce the accepted facts and a merchandise subtotal of 6000 cents.

Suppose the server saves the order as placed, then crashes before saving the item. Each existing field can be individually valid, yet the customer opens an empty receipt. We have found a rule that concerns the records together.

An invariant is a rule every valid state must satisfy. Here: a placed order contains its complete accepted set of items and their purchase facts. The placement operation must make that complete set visible together, or leave the purchase unplaced. A database transaction can provide an all-or-nothing boundary for these writes. It does not establish the accepted price or automatically include an external payment. PostgreSQL's transaction introduction describes that boundary.

Constraints address different rules. Keys can prevent duplicate identities; references can prevent an item from pointing to a missing order; required fields and checks can reject missing purchase facts or quantities that are not positive integers. An item-to-order reference alone cannot require every placed order to have all its items. The write path must preserve that broader rule.

We can now trace the read: find O42, then its items, and use their purchase fields. The later catalog update has no reason to alter that answer. Detailed transaction mechanisms and access-path optimization belong in separate deep dives.

The store now accepts partial returns and shows their history. The customer returns one of the two lamps in O42. Reducing its purchased quantity from two to one would make the original receipt false. The return is another fact about the purchase, with its own occurrence and lifetime.

Add a record for each accepted return:

ReturnRecord(return_id, order_id, line_number,
             quantity, accepted_at)

Return R8 records one unit against (O42, 1). The purchased quantity stays two. A separate accepted return gets its own ID and date; retrying the recording of the same accepted return must resolve to its existing record. A single is_returned field cannot express those separate occurrences.

Each accepted return references an existing order item and records a positive integer quantity. For this simplified requirement, accepted returns are final and their combined quantity cannot exceed the purchased quantity. The write path must preserve that limit even when requests overlap; the model names the rule, while choosing the coordination mechanism is separate work.

Notice the difference from the catalog edit. Changing today's price changed a fact within an existing model. Adding return history changed what the product needs to remember. We extended the model without reinterpreting OrderItem.quantity as “quantity remaining.”

Preservation also has a limit: new fields cannot create missing evidence. If older orders had never captured their purchased titles, adding title_at_purchase would not reveal those titles. Today's catalog title could be different. Unless a trustworthy historical source exists, that value remains unknown; a convenient default must not masquerade as a fact.

Moving existing data into a changed schema introduces migration and compatibility work. We are identifying the meaning that work must preserve, not specifying a migration runbook. Likewise, choosing tables, documents, or indexes should serve these meanings and operations.

Begin with the questions the system must answer. For this store, clarify whether a past receipt should retain agreed prices and titles. Choose the placement write and receipt read as a concrete path through the problem. You can discuss this model alongside APIs and the wider architecture; you do not need to finish every column first.

Draw the essential records and relationships, give each record a meaning, and justify its identity. Explain why an order item exists: this purchase of this product has its own quantity and price. Trace O42 through the sketch. Then identify a state that would violate the requirement, such as a placed order missing its accepted items.

If the interviewer adds partial returns, inspect the earlier meanings before changing fields. Explain why purchased quantity stays fixed and which new fact must be represented. A useful base model gives you something concrete to revise; it is not a schema to memorize for every commerce question.

When comparing alternatives, connect the tradeoff to an operation. Keeping only a product reference is smaller, but cannot recover a price that later changes. Keeping purchase facts supports the receipt and makes checkout responsible for capturing them correctly. State what would make you reconsider, then continue with the broader design.

Try a new dimension of the problem. The store now records one delivery address per order. Customers also keep a default address in their profile. After placing an order, a customer updates that default. The product requires the order to retain the address selected at placement; changing an order's destination is a separate action, outside this exercise.

Sketch the minimum additions and explain where each address belongs. Trace a profile update and an order read. Finally, suppose this feature is added after some orders already exist: what can you honestly display for those older orders?