Food Delivery Orders

Design the schema for a food delivery marketplace: restaurants publish menus with item options (size, add-ons), customers place orders, couriers pick them up, and each order moves through a status lifecycle that customers watch live.

Clarifying Questions

Can one order include items from more than one restaurant?
Do menu items have option groups with limits, like choose one size and up to three toppings?
Must past orders show the names and prices at the time of ordering after the menu changes?
Is courier assignment part of this schema, or a separate dispatch service?
Do restaurants have opening hours and temporary pauses?

Core Requirements

Store restaurants with opening hours and a manual pause switch.
Model menus with categories, items, option groups, and options with price changes.
Place an order that snapshots item names, options, and prices.
Track order status from placed through delivered or canceled, with a timestamp for each step.
Assign a courier and record pickup and dropoff.
List a customer's order history and a restaurant's active orders quickly.

Canonical schema (PostgreSQL)

CREATE TABLE restaurants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  timezone TEXT NOT NULL,
  is_accepting_orders BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE restaurant_hours (
  restaurant_id UUID NOT NULL REFERENCES restaurants(id) ON DELETE CASCADE,
  day_of_week SMALLINT NOT NULL CHECK (day_of_week BETWEEN 0 AND 6),
  opens_at TIME NOT NULL,
  closes_at TIME NOT NULL,
  PRIMARY KEY (restaurant_id, day_of_week, opens_at)
);

CREATE TABLE menu_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  restaurant_id UUID NOT NULL REFERENCES restaurants(id) ON DELETE CASCADE,
  category TEXT NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  base_price_minor INTEGER NOT NULL CHECK (base_price_minor >= 0),
  is_available BOOLEAN NOT NULL DEFAULT true,
  sort_order INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE option_groups (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  menu_item_id UUID NOT NULL REFERENCES menu_items(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  min_select SMALLINT NOT NULL DEFAULT 0,
  max_select SMALLINT NOT NULL,
  CHECK (max_select >= min_select)
);

CREATE TABLE options (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  option_group_id UUID NOT NULL REFERENCES option_groups(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  price_delta_minor INTEGER NOT NULL DEFAULT 0,
  is_available BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  customer_id UUID NOT NULL,
  restaurant_id UUID NOT NULL REFERENCES restaurants(id),
  courier_id UUID,
  status TEXT NOT NULL CHECK (status IN ('placed', 'accepted', 'preparing', 'ready', 'picked_up', 'delivered', 'canceled')),
  subtotal_minor INTEGER NOT NULL,
  delivery_fee_minor INTEGER NOT NULL,
  tip_minor INTEGER NOT NULL DEFAULT 0,
  total_minor INTEGER NOT NULL,
  currency CHAR(3) NOT NULL,
  delivery_address JSONB NOT NULL,
  placed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  id BIGSERIAL PRIMARY KEY,
  order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  menu_item_id UUID REFERENCES menu_items(id) ON DELETE SET NULL,
  item_name TEXT NOT NULL,
  unit_price_minor INTEGER NOT NULL,
  quantity SMALLINT NOT NULL CHECK (quantity > 0),
  selected_options JSONB NOT NULL DEFAULT '[]',
  line_total_minor INTEGER NOT NULL
);

CREATE TABLE order_status_events (
  id BIGSERIAL PRIMARY KEY,
  order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  status TEXT NOT NULL,
  actor_kind TEXT NOT NULL CHECK (actor_kind IN ('customer', 'restaurant', 'courier', 'system')),
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Key Indexes

CREATE INDEX idx_orders_customer_placed ON orders(customer_id, placed_at DESC)

Order history newest first, the most common customer read.

CREATE INDEX idx_orders_restaurant_active ON orders(restaurant_id, placed_at) WHERE status IN ('placed', 'accepted', 'preparing', 'ready')

The restaurant tablet polls its open orders constantly; the partial index ignores the long tail of finished ones.

CREATE INDEX idx_orders_courier_active ON orders(courier_id) WHERE status IN ('ready', 'picked_up')

A courier's current jobs, which is a handful of rows out of millions.

CREATE INDEX idx_menu_items_restaurant ON menu_items(restaurant_id, category, sort_order) WHERE is_available

Renders a menu page in display order from one index range.

CREATE INDEX idx_status_events_order ON order_status_events(order_id, occurred_at)

Builds the live tracking timeline for one order in time order.

Key Decisions to Defend

order_items snapshot item_name, unit_price_minor, and the chosen options, so menu edits never change what a past receipt says.
Selected options are JSONB on the order line: they are always read with the order and never queried alone, so a join table would add cost with no benefit.
Option groups carry min_select and max_select, so 'pick one size' and 'up to three toppings' share one structure that checkout validates.
orders.status holds the current state for fast reads, and order_status_events records each transition for the live tracker and prep-time analytics.
Opening hours live in restaurant_hours alongside the restaurant's timezone; is_accepting_orders is the manual pause that overrides them.

Likely follow-ups

· Support group orders where several people add to one cart.
· Add a promotion like 20% off orders over $30, where do discounts live on the order?
· A restaurant changes a price while 200 carts hold the old one, what happens at checkout?
· Report average prep time per restaurant by hour of day.

Dive Deeper

Food delivery tests the line between a live catalog and an immutable record. Menus change constantly, so every order line must snapshot what the customer actually bought. Interviewers also check that the option model is general, with groups that carry their own limits, and that current status and status history live in separate places, the same split that appears in ticketing and shipping schemas.

All Database Design Prompts