Time

Two people cannot hold the same desk at once

Difficulty 5/5Subscribers

An office assigns desks to people for stretches of time.

An assignment runs from a start date up to, but not including, an end date. An assignment with no end date is open ended: the person still has the desk.

The rules:

No two assignments for the same desk may overlap. Ending on the 10th and starting on the 10th is fine, because the end is exclusive. Ending on the 11th and starting on the 10th is not.
A desk can obviously be reassigned once the previous stretch ends, and two different desks are independent.
An assignment never ends before it starts.
Assignments belong to a desk that exists.

There is no extension available in this database, so the exclusion constraint you may be reaching for is not on the table. The rule still has to hold.

desks 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.

desks
idlabel
assignments
iddesk_idpersonstarts_onends_on

The schema that already exists

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

prelude.sql

CREATE TABLE desks (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  label TEXT NOT NULL UNIQUE
);

INSERT INTO desks (label) VALUES ('A1'), ('A2');

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…