Orders and line items: the classic build, done right

Orders and line items is the most common relational pattern in business software, and the one most often built wrong in a way that only shows up months later — in the revenue numbers.

Here is the correct build, and specifically the one decision that matters most.

The three tables

Products — what you sell. SKU, name, description, current price, category, active flag.

Orders — the transaction. Order number, customer, order date, status, shipping address, notes.

Line items — one record per product on an order. This is the junction table between orders and products, and it is where the money lives.

Everything follows from recognising that line items is not a supporting table. It is the most important table in the system. Products is a catalogue; Orders is a header; Line items is your revenue.

The one decision that matters

On a line item, the unit price must be a stored number, copied from the product at the moment of sale. It must not be a lookup.

This is the whole article, really.

A lookup shows current truth. If your line item looks up the product's price, then when you raise prices next quarter, every historical order silently updates to the new price. Your revenue history rewrites itself. Last year's reports stop matching last year's invoices. Nobody notices immediately, because every number still looks plausible.

Rule: if a value is a snapshot of a moment, store it. If it is current truth, link to it.

So:

The same reasoning applies to tax rate, exchange rate, and the shipping address on the order. Anything that appears on a document you have already sent a customer must be frozen at the moment you sent it.

A useful refinement: keep the stored Unit price and a lookup of Current price. A formula comparing them gives you a price-drift report for free, and makes it obvious if someone sold at the wrong number.

The rollups

On an Order:

• Item count — count of line items.
• Subtotal — sum of Line total.
• Tax — formula: Subtotal × Tax rate (rate frozen on the order).
• Total — formula: Subtotal + Tax + Shipping.
• Fully shipped — And-rollup of line items' Shipped.

On a Product:

• Units sold — sum of line items' Quantity.
• Revenue — sum of line items' Line total.
• Last ordered — max of the order date, via the line item.
• Order count — count of line items.

On a Customer:

• Lifetime value — sum of orders' Total.
• Order count, First order, Last order.
• Average order value — formula from the two above.

Note that line items feed rollups on two different parents at once. That is the point of modelling it properly: one set of facts, summarised from whichever direction the question comes from.

Status, and where it lives

A common mistake is a single status on the order when reality is per-line.

If you can ship part of an order, status belongs on the line item — and the order's status is derived:

• Fully shipped = And-rollup of line Shipped.
• Partially shipped = formula: any shipped AND not all shipped.

If you never split shipments, keep it simple and put status on the order. Do not build for a case you do not have — but know which case you are in before you choose, because retrofitting per-line status onto a year of orders is genuinely unpleasant.

Stock, if you need it

Resist a Stock level field you edit by hand. It will be wrong within a week and there will be no way to find out why.

Instead, a Stock movements table: product, quantity (positive or negative), type (Receipt, Sale, Adjustment, Return), date, and a relation to the line item or purchase order that caused it.

Then Stock on hand is a sum rollup of movements. It cannot be wrong, because it is not stored — and every number has a traceable cause. Full treatment in build an inventory system.

Numbering

Order numbers need to be sequential, human-readable and permanent: ORD-1043.

Two rules, both non-negotiable:

• Never reuse a number, even after cancellation. Two documents sharing a reference is an audit problem.
• Never renumber. Sorting must not change existing numbers.

Use an auto-number field, not a formula based on row position. Formulas based on position renumber when you sort, which is exactly the failure to avoid. See why every table needs a stable ID.

The views

• Open orders — grid filtered to not-shipped, sorted by order date. The fulfilment queue.
• Picking list — line items grouped by product, filtered to unshipped. Warehouse view: what to pull, in one place.
• Revenue by month — orders grouped by month with summed totals.
• Top products — products sorted by revenue rollup, descending.
• Slow movers — products where Last ordered is over 90 days ago, or empty.

The picking list is worth highlighting: it is the same line item records as the order view, grouped differently. In a spreadsheet that is a separate sheet someone maintains. Here it is a saved view that is never out of date.

The checklist

Before you call it done:

• Unit price on line items is stored, not looked up.
• Tax rate and exchange rate are frozen on the order.
• Order totals are rollups, never typed.
• Stock, if tracked, is a rollup of movements, not an edited number.
• Order numbers are auto-numbers, never reused, never renumbered.
• Shipping address is a copy on the order, not a live lookup to the customer.

Every one of these is about the same thing: the difference between what is true now and what was true then. Get that right and the system stays honest for years. Get it wrong and it corrupts quietly, in the direction of whatever your prices did.