OLTP schema design interviews

1CREATE TABLE users (2  user_id        BIGSERIAL PRIMARY KEY,3  email          CITEXT NOT NULL UNIQUE,4  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),5  deleted_at     TIMESTAMPTZ6);78CREATE TABLE orders (9  order_id       BIGSERIAL PRIMARY KEY,10  user_id        BIGINT NOT NULL REFERENCES users(user_id),11  status         TEXT NOT NULL CHECK (status IN ('pending','paid','cancelled','refunded')),12  currency       CHAR(3) NOT NULL,13  amount_cents   BIGINT NOT NULL CHECK (amount_cents >= 0),14  created_at     TIMESTAMPTZ NOT NULL DEFAULT now()15);16CREATE INDEX orders_user_created_idx ON orders (user_id, created_at DESC);

1-- Normalized: order_items reference products2CREATE TABLE order_items (3  order_id     BIGINT NOT NULL REFERENCES orders(order_id),4  product_id   BIGINT NOT NULL REFERENCES products(product_id),5  qty          INT NOT NULL CHECK (qty > 0),6  -- snapshot price at purchase time (intentional denorm)7  unit_price_cents BIGINT NOT NULL,8  PRIMARY KEY (order_id, product_id)9);10-- Why snapshot price? Product price changes must not rewrite history.

1CREATE TABLE courses (2  course_id BIGSERIAL PRIMARY KEY,3  slug TEXT NOT NULL UNIQUE4);5CREATE TABLE enrollments (6  user_id BIGINT NOT NULL REFERENCES users(user_id),7  course_id BIGINT NOT NULL REFERENCES courses(course_id),8  enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),9  status TEXT NOT NULL,10  PRIMARY KEY (user_id, course_id)11);12CREATE INDEX enrollments_course_idx ON enrollments (course_id, enrolled_at);

1-- Partial unique: email unique among live users2CREATE UNIQUE INDEX users_email_live_uidx3  ON users (email)4  WHERE deleted_at IS NULL;56-- Audit log sketch7CREATE TABLE audit_log (8  id BIGSERIAL PRIMARY KEY,9  actor_id BIGINT,10  entity_type TEXT NOT NULL,11  entity_id BIGINT NOT NULL,12  action TEXT NOT NULL,13  at TIMESTAMPTZ NOT NULL DEFAULT now(),14  payload JSONB15);

1CREATE TABLE tickets (2  ticket_id BIGSERIAL PRIMARY KEY,3  tenant_id BIGINT NOT NULL REFERENCES tenants(tenant_id),4  title TEXT NOT NULL,5  status TEXT NOT NULL,6  created_at TIMESTAMPTZ NOT NULL DEFAULT now()7);8CREATE INDEX tickets_tenant_created_idx9  ON tickets (tenant_id, created_at DESC);10-- Every query: WHERE tenant_id = :current

1-- Prompt: riders, drivers, trips, payments, ratings23riders(rider_id PK, ...)4drivers(driver_id PK, ...)5trips(6  trip_id PK,7  rider_id FK, driver_id FK,8  status, requested_at, completed_at,9  pickup_cell, dropoff_cell,10  price_cents, currency11)12payments(13  payment_id PK, trip_id FK UNIQUE,  -- 1:1 with trip for MVP14  provider_ref UNIQUE,15  status, amount_cents16)17ratings(18  trip_id FK, rater_role, score,19  PRIMARY KEY (trip_id, rater_role)  -- rider rates driver & vice versa20)21-- Hot queries: trips by rider recent; driver earnings by day;22-- open trips by geo cell. Index accordingly.