A price that changes without erasing what it was

Difficulty 3/5Subscribers

New to this? Learn it first: History: keep every version

A shop changes its prices. Not often, but it does, and three teams need different things from that.

Support reopens an invoice from March and has to see the price that was charged then, not the price today.
Finance asks what any product cost on any given date, including dates in the past and dates between two changes.
The catalogue shows today's price, and a price change must take effect on a date the team picks, sometimes scheduled ahead of time.

Two more rules the data has to enforce on its own:

A price is never negative.
One product cannot have two different prices starting on the same day. That is a data-entry mistake, and the database should say so.

Before its first price is set, a product simply has no price, and that is a valid state, not an error.

The products table already exists.

The contract

2 relations

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

products
idsku
product_prices
idproduct_idprice_centsvalid_from

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
);

INSERT INTO products (sku, name) VALUES ('WIDGET', 'Widget'), ('GADGET', 'Gadget');

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…