The invoice that must not change when the price does

Difficulty 4/5Subscribers

New to this? Learn it first: The price paid: a fact frozen at a moment New to this? Learn it first: Level 2 boss: the marketplace New to this? Learn it first: Level 3 boss: normalise a flat export

A shop takes orders. Products have a current price, and that price changes.

What the business needs:

An order records which products, how many. Nothing surprising there.
An order's total is what the customer was actually charged. Raising a price today must not change a single order that was already placed.
Nobody types the price in by hand when ordering. The order is placed with a product and a quantity, and the right amount has to come from somewhere.
A quantity is always at least one, and a line belongs to an order that exists.

How you get there is yours. The tests never read your tables directly — they ask a view called order_totals for one column, total_cents, per order_id. Providing that view is part of the answer.

products and orders already exist.

The contract

3 relations

The tests reference these names. Everything else is yours, and is what is being assessed: extra tables, extra columns, types, constraints, indexes.

orders
id
order_lines
idorder_idproduct_idquantity
order_totals
order_idtotal_cents

The schema that already exists

This runs before your submission. Do not repeat it, extend it.

prelude.sql

CREATE TABLE products (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         TEXT NOT NULL UNIQUE,
  name        TEXT NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0)
);

CREATE TABLE orders (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  placed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO products (sku, name, price_cents) VALUES ('WIDGET', 'Widget', 1000);
INSERT INTO orders DEFAULT VALUES;

CREATE TABLE ...  (
  id          BIGINT GENERATED ALWAYS AS IDENTITY,
  tenant_id   BIGINT NOT NULL REFERENCES ...,
  ...         TEXT NOT NULL,
  valid_from  TIMESTAMPTZ NOT NULL,
  valid_until TIMESTAMPTZ
);

CREATE UNIQUE INDEX ... ON ... (...)
  WHERE ... IS NULL;

LOCKED

This one is for subscribers

The problem above is the whole problem, and nothing is hidden from it. What a subscription adds is the part that tells you whether your answer holds: a real Postgres runs your schema, a battery of hidden tests decides, and a design review reads what you wrote.

See the plans Or try one of the free ones

Checking your subscription…