Food Delivery ER Diagram

A food delivery schema follows an order from a restaurant menu to a customer's door. Orders snapshot item prices, deliveries pair each order with a courier and timestamps, and addresses keep delivery destinations separate from customer identities.

The order line is where history is protected. order_items stores unit_price and item_name copied from menu_items at checkout, so renaming a dish or raising its price never rewrites past orders. menu_items still references menu_categories, which references restaurants, giving each location its own menu structure. orders also snapshots address_id rather than joining through customers at read time, because a customer may move or delete an old address while the delivery record must still say where the food went. This makes orders a point-in-time record rather than a live view of the catalog.

Read the diagram as three flows meeting at orders. The catalog flow runs restaurants to menu_categories to menu_items, then into order_items. The customer flow runs customers to addresses and on to orders. The fulfilment flow runs orders to deliveries and couriers. deliveries.order_id is UNIQUE, so each order has one delivery record, updated in place as picked_up_at and delivered_at are filled; the CHECK requiring picked_up_at before delivered_at keeps the timeline honest. couriers carry vehicle_type and active, which is what dispatch queries filter on. The same record separates an unassigned delivery from one already on the road.

Real delivery systems break in the gap between status and money. orders.total is stored, but nothing in this schema recomputes or checks it against the sum of order_items, so a totals mismatch is possible; the fix is a transaction or trigger that writes both together. Deleting a restaurant or menu item with order history is blocked by the plain foreign keys, which is correct. Deleting an order cascades to its items and delivery, so a cancelled test order disappears cleanly — but production systems usually keep cancelled orders for accounting instead.

The first extension is usually a delivery_status_history table (delivery_id, status, changed_at), because one status column cannot answer how long an order sat waiting for pickup. A promotions table with order_id or order_items.discount_amount records discounts without hiding them in the total. Courier shifts and zones enable dispatch scoring, simplified here to a courier_zones table keyed on postal codes. If customers can order from one restaurant only, payments belongs as a one-to-one sibling of orders, mirroring the hotel and e-commerce examples. Each addition introduces a table and a foreign key without disturbing the order snapshot that already protects history.

The diagram

Tables (9)

Relationships (10)

• 1:Nrestaurants.id→menu_categories.restaurant_idON DELETE CASCADE
• 1:Nmenu_categories.id→menu_items.menu_category_idON DELETE CASCADE
• 1:Ncustomers.id→addresses.customer_idON DELETE CASCADE
• 1:Ncustomers.id→orders.customer_id
• 1:Nrestaurants.id→orders.restaurant_id
• 1:Naddresses.id→orders.address_id
• 1:Norders.id→order_items.order_idON DELETE CASCADE
• 1:Nmenu_items.id→order_items.menu_item_id
• 1:1orders.id→deliveries.order_idON DELETE CASCADE
• 1:Ncouriers.id→deliveries.courier_id

The SQL

CREATE TABLE restaurants (
  id SERIAL PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  city VARCHAR(80) NOT NULL,
  phone VARCHAR(30)
);

CREATE TABLE menu_categories (
  id SERIAL PRIMARY KEY,
  restaurant_id INTEGER NOT NULL REFERENCES restaurants(id) ON DELETE CASCADE,
  name VARCHAR(80) NOT NULL,
  display_order INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE menu_items (
  id SERIAL PRIMARY KEY,
  menu_category_id INTEGER NOT NULL REFERENCES menu_categories(id) ON DELETE CASCADE,
  name VARCHAR(120) NOT NULL,
  description TEXT,
  price DECIMAL(8,2) NOT NULL CHECK (price >= 0),
  available BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  phone VARCHAR(30)
);

CREATE TABLE addresses (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  line1 VARCHAR(200) NOT NULL,
  city VARCHAR(80) NOT NULL,
  postal_code VARCHAR(20) NOT NULL,
  is_default BOOLEAN NOT NULL DEFAULT false
);

CREATE TABLE couriers (
  id SERIAL PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  phone VARCHAR(30),
  vehicle_type VARCHAR(20) NOT NULL DEFAULT 'bike',
  active BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  restaurant_id INTEGER NOT NULL REFERENCES restaurants(id),
  address_id INTEGER NOT NULL REFERENCES addresses(id),
  status VARCHAR(20) NOT NULL DEFAULT 'placed',
  total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
  placed_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  menu_item_id INTEGER NOT NULL REFERENCES menu_items(id),
  item_name VARCHAR(120) NOT NULL,
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price DECIMAL(8,2) NOT NULL CHECK (unit_price >= 0)
);

CREATE TABLE deliveries (
  id SERIAL PRIMARY KEY,
  order_id INTEGER NOT NULL UNIQUE REFERENCES orders(id) ON DELETE CASCADE,
  courier_id INTEGER REFERENCES couriers(id),
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  picked_up_at TIMESTAMP,
  delivered_at TIMESTAMP,
  CHECK (delivered_at IS NULL OR picked_up_at IS NOT NULL)
);

Keep learning

• What Is an ER Diagram?
• How to Create an ER Diagram
• SQL to ER Diagram: The Complete Guide
• Primary Key vs Foreign Key
• All guides