The day it happened depends on where you are standing

Difficulty 4/5Subscribers

New to this? Learn it first: Types that tell the truth New to this? Learn it first: Level 2 boss: the marketplace

A booking platform runs venues in different countries.

Two pieces of time, and they are not the same kind of thing:

When a booking was confirmed. A single moment. Two bookings confirmed seconds apart must sort correctly no matter which country each was made from, and support has to be able to say which came first.
Which day the booking is for. A local calendar day at the venue. A booking on the 5th of March in Tokyo is on the 5th of March, and stays the 5th for a viewer in São Paulo, in London, and for the venue itself. It is not a moment; it is a square on a wall calendar.

Also:

Each venue has a time zone, and the schema has to know it, because a local day means nothing without knowing whose.
A venue takes at most one booking per local day.
Bookings belong to a venue that exists.

venues already exists, with its time zones.

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.

venues
idnametime_zone
bookings
idvenue_idconfirmed_atbooked_day

The schema that already exists

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

prelude.sql

CREATE TABLE venues (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name      TEXT NOT NULL,
  time_zone TEXT NOT NULL
);

INSERT INTO venues (name, time_zone) VALUES
  ('Tokyo Hall', 'Asia/Tokyo'),
  ('Sao Paulo Room', 'America/Sao_Paulo');

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…