Time

A price that changes without erasing what it was

Difficulty 3/5Free

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

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

Your schemaPostgreSQL DDL: tables, constraints, indexes

schema.sql

CREATE TABLE product_prices (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id  BIGINT NOT NULL,
  price_cents INTEGER NOT NULL,
  valid_from  DATE NOT NULL
);

-- products already exists. Wire the prices to it and make the rules hold.

Tab indents · ⌘/Ctrl + Enter runs

What will be checked

8 tests

····Finance can ask what the Widget cost in March
····And what it cost after the price went up
····Before its first price, the Widget had none
····Both prices are still on record
····A price can be scheduled for a different product
····A negative price is refused
····One product cannot have two prices starting the same day
····A price cannot belong to a product that does not exist