One column must be copied, the other must not

Difficulty 4/5Subscribers

New to this? Learn it first: Level 2 boss: the marketplace New to this? Learn it first: Anomalies: what goes wrong when a fact repeats New to this? Learn it first: Second normal form: the whole key New to this? Learn it first: Third normal form: nothing but the key New to this? Learn it first: Level 3 boss: normalise a flat export

A shop ships orders to customers, and customers move house and change email.

Two rules that sound similar and are opposites:

Where the parcel went is history. If a customer moves after their order shipped, the shipment must still show the address it was actually delivered to. Support answers "where did my package go" with that.
How to reach the customer is current. If a customer changes their email, every shipment, including old ones, must show the new address, because that is where the notification goes today.

The rest:

A shipment belongs to a customer that exists.
The tests read both through a view you provide, shipment_contact, with shipment_id, ship_to and notify_email. How you get those two values is the answer.

customers already exists.

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.

customers
idemailaddress
shipments
idcustomer_id
shipment_contact
shipment_idship_tonotify_email

The schema that already exists

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

prelude.sql

CREATE TABLE customers (
  id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email   TEXT NOT NULL,
  address TEXT NOT NULL
);

INSERT INTO customers (email, address) VALUES ('ana@example.com', '10 Old Street');

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…