The invoice that must not change when the price does: schema design exercise

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 one thing that is fixed

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;

Your schemaPostgreSQL DDL: tables, constraints, indexes

schema.sql

CREATE TABLE order_lines (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id   BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity   INTEGER NOT NULL
);

-- An order is placed with a product and a quantity only. Decide where the
-- amount charged comes from, then publish order_totals(order_id, total_cents).

Tab indents · ⌘/Ctrl + Enter runs

What will be checked

6 tests

····An order line is placed with just a product and a quantity
····The total is two Widgets at the price of the day
····The shop raises the price of the Widget
····The order already placed still totals what it did
····A line cannot order zero of something
····A line cannot belong to an order that does not exist