Crowsfoot

Food delivery database schema: ER diagram and SQL

A delivery app is an order system with one twist: every order belongs to exactly one restaurant. This schema keeps menus per restaurant and order lines that tie an order to the items in it.

Open this schema in the editor Jump to the SQL
customers PK id bigint name varchar phone varchar restaurants PK id bigint name varchar cuisine varchar menu_items PK id bigint FK restaurant_id bigint name varchar price numeric orders PK id bigint FK customer_id bigint FK restaurant_id bigint status varchar placed_at timestamp order_lines PK order_id bigint PK menu_item_id bigint quantity int

Tables in this schema

customers

People who order food.

restaurants

Places that prepare the food.

menu_items

Dishes a restaurant sells, each with a price.

orders

One order from one customer to one restaurant.

order_lines

The dishes in an order and how many of each. Links orders and menu items.

How the tables relate

  • menu_items.restaurant_id references restaurants.id: one row in restaurants can have many rows in menu_items.
  • orders.customer_id references customers.id: one row in customers can have many rows in orders.
  • orders.restaurant_id references restaurants.id: one row in restaurants can have many rows in orders.
  • order_lines is a junction table between orders and menu_items: many-to-many.

SQL (PostgreSQL)

Copy it into your database, or open it in Crowsfoot to edit the diagram, add notes, and export again.

CREATE TABLE customers (
  id bigint PRIMARY KEY,
  name varchar(120) NOT NULL,
  phone varchar(30) NOT NULL
);

CREATE TABLE restaurants (
  id bigint PRIMARY KEY,
  name varchar(160) NOT NULL,
  cuisine varchar(60) NOT NULL
);

CREATE TABLE menu_items (
  id bigint PRIMARY KEY,
  restaurant_id bigint NOT NULL REFERENCES restaurants (id),
  name varchar(160) NOT NULL,
  price numeric(8,2) NOT NULL
);

CREATE TABLE orders (
  id bigint PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id),
  restaurant_id bigint NOT NULL REFERENCES restaurants (id),
  status varchar(20) NOT NULL DEFAULT 'placed',
  placed_at timestamptz NOT NULL
);

CREATE TABLE order_lines (
  order_id bigint NOT NULL REFERENCES orders (id),
  menu_item_id bigint NOT NULL REFERENCES menu_items (id),
  quantity int NOT NULL,
  PRIMARY KEY (order_id, menu_item_id)
);

Design notes

  • Add a check, or a composite foreign key, so an order can only contain items from the same restaurant as the order.
  • Copy the price onto order_lines when you add couriers and fees, so an old order keeps its original total.
  • Delivery tracking usually becomes its own table (couriers and deliveries) linked to orders.

More example schemas

New to ER diagrams? Read the crow’s foot notation guide.