Designing a Food Delivery Database Schema in PostgreSQL

In this guide we'll design a realistic PostgreSQL schema for a food delivery platform — similar to Uber Eats or DoorDash. We'll keep the design normalised, choose PostgreSQL-native types throughout, and explain the key trade-offs so you understand why each decision was made, not just what it looks like.

What we'll cover:

Identifying the core entities and their relationships
Walking through the full PostgreSQL DDL (10 tables + indexes)
Reading an ASCII ER diagram
Running five real-world example queries
Ideas for extending the schema in production

Step 1: Identify the Entities

Table	Purpose
`users`	All platform participants — customers, restaurant owners, drivers, and admins in a single table with a role column
`restaurants`	Restaurant listings with location, opening hours, and active status
`menu_categories`	Organises menu items into named groups (e.g. Starters, Mains, Drinks)
`menu_items`	Individual dishes or products with price and availability flag
`driver_profiles`	Vehicle and availability information for users with the DRIVER role
`orders`	Customer orders, linking a customer to a restaurant with full status lifecycle
`order_items`	Line items within an order — which items, how many, at what price
`deliveries`	Assigns a driver to an order and tracks pickup and drop-off timestamps
`payments`	Payment method and status for each order
`reviews`	Customer feedback on a restaurant or driver, anchored to a specific order

Step 2: Define the Relationships

A users record with role = 'RESTAURANT_OWNER' links to one or more restaurants
Each restaurant has one or more menu_categories, each containing many menu_items
A customer (users) places many orders; each order targets one restaurant
Each order contains one or more order_items drawn from that restaurant's menu_items
Each order has at most one delivery, assigned to one driver (driver_profiles)
Each order has at most one payment record
A customer may leave one review per order, rating either the restaurant or the driver (or both)

Step 3: PostgreSQL DDL

A few design decisions worth noting up front:

Single users table with a role column — avoids the table-per-role anti-pattern and keeps JOINs simple
VARCHAR + CHECK instead of ENUM — adding a new role or status never requires an ALTER TYPE migration
driver_profiles as a profile extension — keeps vehicle-specific columns out of users while preserving a clean 1:1 relationship
NUMERIC for money — exact decimal arithmetic; never FLOAT or REAL for currency
TIMESTAMPTZ everywhere — stores all timestamps with timezone, essential for a multi-region service
UUID primary keys — opaque, safe to expose in APIs, avoids sequential-ID enumeration

-- Enable pgcrypto for UUID generation
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ──────────────────────────────────────────────────────────────────
-- USERS
-- ──────────────────────────────────────────────────────────────────
CREATE TABLE users (
  user_id    UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  full_name  VARCHAR(100) NOT NULL,
  email      VARCHAR(255) UNIQUE NOT NULL,
  phone      VARCHAR(20),
  role       VARCHAR(20)  NOT NULL DEFAULT 'CUSTOMER'
               CHECK (role IN ('CUSTOMER','RESTAURANT_OWNER','DRIVER','ADMIN')),
  is_active  BOOLEAN      NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- ──────────────────────────────────────────────────────────────────
-- RESTAURANTS
-- ──────────────────────────────────────────────────────────────────
CREATE TABLE restaurants (
  restaurant_id     UUID          PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id          UUID          NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
  name              VARCHAR(150)  NOT NULL,
  description       TEXT,
  address           TEXT          NOT NULL,
  city              VARCHAR(100),
  country           VARCHAR(100),
  latitude          NUMERIC(10,8),
  longitude         NUMERIC(11,8),
  phone             VARCHAR(20),
  opening_time      TIME,
  closing_time      TIME,
  is_active         BOOLEAN       NOT NULL DEFAULT TRUE,
  avg_prep_time_min INT           NOT NULL DEFAULT 30,
  created_at        TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

-- ──────────────────────────────────────────────────────────────────
-- MENU CATEGORIES
-- ──────────────────────────────────────────────────────────────────
CREATE TABLE menu_categories (
  category_id   UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  restaurant_id UUID         NOT NULL REFERENCES restaurants(restaurant_id) ON DELETE CASCADE,
  name          VARCHAR(100) NOT NULL,
  sort_order    INT          NOT NULL DEFAULT 0
);

-- ──────────────────────────────────────────────────────────────────
-- MENU ITEMS
-- ──────────────────────────────────────────────────────────────────
CREATE TABLE menu_items (
  item_id       UUID          PRIMARY KEY DEFAULT gen_random_uuid(),
  category_id   UUID          NOT NULL REFERENCES menu_categories(category_id) ON DELETE CASCADE,
  restaurant_id UUID          NOT NULL REFERENCES restaurants(restaurant_id) ON DELETE CASCADE,
  name          VARCHAR(150)  NOT NULL,
  description   TEXT,
  price         NUMERIC(10,2) NOT NULL,
  image_url     VARCHAR(500),
  is_available  BOOLEAN       NOT NULL DEFAULT TRUE,
  created_at    TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

-- ──────────────────────────────────────────────────────────────────
-- DRIVER PROFILES  (extends users where role = 'DRIVER')
-- ──────────────────────────────────────────────────────────────────
CREATE TABLE driver_profiles (
  driver_id     UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id       UUID        NOT NULL UNIQUE REFERENCES users(user_id) ON DELETE CASCADE,
  vehicle_type  VARCHAR(20) NOT NULL CHECK (vehicle_type IN ('BICYCLE','MOTORCYCLE','CAR')),
  license_plate VARCHAR(20),
  is_available  BOOLEAN     NOT NULL DEFAULT TRUE,
  latitude      NUMERIC(10,8),
  longitude     NUMERIC(11,8)
);

-- ──────────────────────────────────────────────────────────────────
-- ORDERS
-- ──────────────────────────────────────────────────────────────────
CREATE TABLE orders (
  order_id            UUID          PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id             UUID          NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
  restaurant_id       UUID          NOT NULL REFERENCES restaurants(restaurant_id),
  status              VARCHAR(20)   NOT NULL DEFAULT 'PENDING'
                        CHECK (status IN ('PENDING','CONFIRMED','PREPARING','READY',
                                          'PICKED_UP','DELIVERED','CANCELLED')),
  subtotal            NUMERIC(10,2) NOT NULL,
  delivery_fee        NUMERIC(10,2) NOT NULL DEFAULT 0.00,
  total_amount        NUMERIC(10,2) NOT NULL,
  delivery_address    TEXT          NOT NULL,
  delivery_latitude   NUMERIC(10,8),
  delivery_longitude  NUMERIC(11,8),
  notes               TEXT,
  created_at          TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  updated_at          TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

-- ──────────────────────────────────────────────────────────────────
-- ORDER ITEMS
-- ──────────────────────────────────────────────────────────────────
CREATE TABLE order_items (
  order_item_id        UUID          PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id             UUID          NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
  item_id              UUID          NOT NULL REFERENCES menu_items(item_id),
  quantity             INT           NOT NULL DEFAULT 1,
  unit_price           NUMERIC(10,2) NOT NULL,
  special_instructions TEXT
);

-- ──────────────────────────────────────────────────────────────────
-- DELIVERIES
-- ──────────────────────────────────────────────────────────────────
CREATE TABLE deliveries (
  delivery_id            UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id               UUID        NOT NULL UNIQUE REFERENCES orders(order_id) ON DELETE CASCADE,
  driver_id              UUID        NOT NULL REFERENCES driver_profiles(driver_id),
  status                 VARCHAR(20) NOT NULL DEFAULT 'ASSIGNED'
                           CHECK (status IN ('ASSIGNED','PICKED_UP','IN_TRANSIT','DELIVERED')),
  assigned_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  picked_up_at           TIMESTAMPTZ,
  delivered_at           TIMESTAMPTZ,
  estimated_delivery_min INT
);

-- ──────────────────────────────────────────────────────────────────
-- PAYMENTS
-- ──────────────────────────────────────────────────────────────────
CREATE TABLE payments (
  payment_id     UUID          PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id       UUID          NOT NULL UNIQUE REFERENCES orders(order_id) ON DELETE CASCADE,
  amount         NUMERIC(10,2) NOT NULL,
  payment_method VARCHAR(20)   NOT NULL
                   CHECK (payment_method IN ('CARD','MOBILE_MONEY','CASH','APPLE_PAY','GOOGLE_PAY')),
  payment_status VARCHAR(20)   NOT NULL DEFAULT 'PENDING'
                   CHECK (payment_status IN ('PENDING','COMPLETED','FAILED','REFUNDED')),
  processed_at   TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

-- ──────────────────────────────────────────────────────────────────
-- REVIEWS  (one per user per order)
-- ──────────────────────────────────────────────────────────────────
CREATE TABLE reviews (
  review_id     UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id       UUID        NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
  order_id      UUID        NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
  restaurant_id UUID        REFERENCES restaurants(restaurant_id),
  driver_id     UUID        REFERENCES driver_profiles(driver_id),
  rating        SMALLINT    NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment       TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (user_id, order_id)
);

-- ──────────────────────────────────────────────────────────────────
-- INDEXES
-- ──────────────────────────────────────────────────────────────────
CREATE INDEX idx_restaurants_city      ON restaurants(city);
CREATE INDEX idx_restaurants_owner     ON restaurants(owner_id);
CREATE INDEX idx_menu_items_category   ON menu_items(category_id);
CREATE INDEX idx_menu_items_available  ON menu_items(restaurant_id, is_available);
CREATE INDEX idx_orders_user           ON orders(user_id);
CREATE INDEX idx_orders_restaurant     ON orders(restaurant_id);
CREATE INDEX idx_orders_status         ON orders(status);
CREATE INDEX idx_orders_created        ON orders(created_at DESC);
CREATE INDEX idx_deliveries_driver     ON deliveries(driver_id);
CREATE INDEX idx_reviews_restaurant    ON reviews(restaurant_id);

DBA Tip: The UNIQUE constraint on deliveries.order_id enforces a one-delivery-per-order rule at the database level — no application-side check required. Similarly, UNIQUE (user_id, order_id) on reviews prevents duplicate review submissions without needing a SELECT before every INSERT.

Step 4: ER Diagram Overview

Scroll or pinch to zoom · drag to pan · double-click to zoom in

Entity-relationship diagram for the food delivery platform

Entity-relationship diagram — food delivery platform

Step 5: Example Queries

1. Open Restaurants in a City Right Now

Combine the active flag, opening hours, and a rating summary in one query:

SELECT
  r.restaurant_id,
  r.name                    AS restaurant_name,
  r.avg_prep_time_min,
  ROUND(AVG(rv.rating), 2)  AS avg_rating,
  COUNT(rv.review_id)       AS total_reviews
FROM restaurants r
LEFT JOIN reviews rv ON rv.restaurant_id = r.restaurant_id
WHERE r.city      = 'London'
  AND r.is_active = TRUE
  AND CURRENT_TIME BETWEEN r.opening_time AND r.closing_time
GROUP BY r.restaurant_id, r.name, r.avg_prep_time_min
ORDER BY avg_rating DESC NULLS LAST, r.name;

DBA Tip: CURRENT_TIME is a PostgreSQL built-in that returns the current time-of-day in the session's timezone — no CURTIME() or NOW()::time gymnastics needed.

2. Full Menu for a Restaurant

Return all available items grouped by category display order:

SELECT
  mc.name  AS category,
  mi.name  AS item_name,
  mi.price,
  mi.description
FROM menu_items mi
JOIN menu_categories mc ON mi.category_id = mc.category_id
WHERE mi.restaurant_id = '<restaurant_uuid>'
  AND mi.is_available  = TRUE
ORDER BY mc.sort_order, mi.name;

3. Live Order Tracking

Return the full lifecycle of a single order including delivery and driver contact details:

SELECT
  o.order_id,
  o.status              AS order_status,
  o.total_amount,
  d.status              AS delivery_status,
  u.full_name           AS driver_name,
  u.phone               AS driver_phone,
  d.estimated_delivery_min,
  d.picked_up_at,
  d.delivered_at
FROM orders o
LEFT JOIN deliveries     d  ON o.order_id = d.order_id
LEFT JOIN driver_profiles dp ON d.driver_id = dp.driver_id
LEFT JOIN users           u  ON dp.user_id  = u.user_id
WHERE o.order_id = '<order_uuid>';

4. Top 5 Highest-Rated Restaurants

Filter to restaurants with meaningful review volume before ranking:

SELECT
  r.restaurant_id,
  r.name                    AS restaurant_name,
  r.city,
  ROUND(AVG(rv.rating), 2)  AS avg_rating,
  COUNT(rv.review_id)       AS total_reviews
FROM reviews rv
JOIN restaurants r ON rv.restaurant_id = r.restaurant_id
GROUP BY r.restaurant_id, r.name, r.city
HAVING COUNT(rv.review_id) >= 10
ORDER BY avg_rating DESC
LIMIT 5;

5. Monthly Revenue per Restaurant

DATE_TRUNC clips the timestamp to the start of the current month — the PostgreSQL equivalent of MySQL's DATE_FORMAT(NOW(), '%Y-%m-01'):

SELECT
  r.name                AS restaurant_name,
  COUNT(o.order_id)     AS orders_delivered,
  SUM(o.subtotal)       AS gross_revenue,
  SUM(o.delivery_fee)   AS delivery_fees,
  SUM(o.total_amount)   AS total_revenue
FROM orders o
JOIN restaurants r ON o.restaurant_id = r.restaurant_id
WHERE o.status     = 'DELIVERED'
  AND o.created_at >= DATE_TRUNC('month', NOW())
GROUP BY r.restaurant_id, r.name
ORDER BY total_revenue DESC;

Extending the Schema

Feature	Approach
Promo codes	`promo_codes` (discount type, value, usage limit, expiry) + `order_promos` junction; apply discount at order creation and store the saved amount
Real-time driver tracking	A separate `driver_locations` event table with `(driver_id, latitude, longitude, recorded_at)`; query the latest row per driver
Surge pricing	`pricing_rules` with a time-window and multiplier; resolved at order-creation time and stored as `orders.surge_multiplier`
Loyalty points	`loyalty_transactions` debiting/crediting points per order; running balance stored on the `users` record
Push notifications	`notification_log` with event type, channel (push/SMS/email), payload, and delivery status
Multi-restaurant orders	Add an `order_restaurants` junction and split `order_items` per restaurant sub-order

Summary

We've built a complete food delivery schema in PostgreSQL — 10 normalised tables, targeted indexes, and a set of example queries you can run directly against a real database. The schema handles the full order lifecycle: browsing a menu, placing an order, tracking a driver, processing payment, and leaving a review.

Because status columns use VARCHAR + CHECK constraints and UUID foreign keys, the schema is straightforward to extend without DDL migrations every time a new status value or role is added. Try it in SQLExplain.ai to explore the relationships interactively.