Restaurant Ordering ER Diagram

A restaurant ordering system mirrors the e-commerce pattern in a smaller space: a menu of items, orders that snapshot prices at sale time, and reservations holding tables for future guests.

menu_items.available is the quiet design decision here: taking an item off the menu means flipping it to false, not deleting the row, because past order_items reference menu_item_id and the schema — like the e-commerce example — protects that history with a plain foreign key. Prices are snapshotted onto unit_price at order time for the same reason: tomorrow's price change cannot restate last night's ticket. menu_categories belongs to a specific restaurant, so two locations can each define their own menu structure without collision.

The diagram reads as two flows sharing one customers table. The ordering path goes restaurants to menu_categories to menu_items, then orders and order_items underneath. orders.order_type defaults to 'dine-in' but carries delivery and takeaway in the same table, and customer_id is nullable because walk-ins and phone orders exist without an account. The reservation path is shorter: reservations ties a customer to a restaurant with a reserved_at timestamp and a party_size defaulting to two — no room or table attached.

The conspicuous absence is tables — the furniture. reservations knows the restaurant and the time but nothing about which physical table seats the party, so seating is currently an application-level convention. Adding a tables table (restaurant_id, label, seats) and a reservation_tables join is the natural fix, and it is also what enables capacity checks before confirming a reservation. Note the asymmetry in the SQL: orders.customer_id is nullable but reservations.customer_id is not — an anonymous order is normal, a reservation without a known customer is unmanageable.

staff exists but is not yet wired to anything, and that is the next join to make: an orders.taken_by_id column or a shifts table turns staff records into accountability for who placed which order. Payments are the other missing piece — orders carry no money of their own beyond the sum of order_items, so a payments table (order_id, amount, method) mirrors the one-to-one pattern used on the e-commerce and hotel examples. Modifiers — extra cheese, no onions — would become a modifiers table joined to order_items.

The diagram

Tables (8)

Relationships (9)

• 1:Nrestaurants.id→menu_categories.restaurant_idON DELETE CASCADE
• 1:Nmenu_categories.id→menu_items.menu_category_idON DELETE CASCADE
• 1:Nrestaurants.id→orders.restaurant_id
• 1:Ncustomers.id→orders.customer_id
• 1:Norders.id→order_items.order_idON DELETE CASCADE
• 1:Nmenu_items.id→order_items.menu_item_id
• 1:Nrestaurants.id→reservations.restaurant_id
• 1:Ncustomers.id→reservations.customer_id
• 1:Nrestaurants.id→staff.restaurant_idON DELETE CASCADE

The SQL

CREATE TABLE restaurants (
  id SERIAL PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  address VARCHAR(200) 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
);

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,
  available BOOLEAN NOT NULL DEFAULT true
);

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

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  restaurant_id INTEGER NOT NULL REFERENCES restaurants(id),
  customer_id INTEGER REFERENCES customers(id),
  order_type VARCHAR(15) NOT NULL DEFAULT 'dine-in',
  status VARCHAR(20) NOT NULL DEFAULT 'placed',
  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),
  quantity INTEGER NOT NULL DEFAULT 1,
  unit_price DECIMAL(8,2) NOT NULL
);

CREATE TABLE reservations (
  id SERIAL PRIMARY KEY,
  restaurant_id INTEGER NOT NULL REFERENCES restaurants(id),
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  reserved_at TIMESTAMP NOT NULL,
  party_size INTEGER NOT NULL DEFAULT 2,
  status VARCHAR(20) NOT NULL DEFAULT 'confirmed'
);

CREATE TABLE staff (
  id SERIAL PRIMARY KEY,
  restaurant_id INTEGER NOT NULL REFERENCES restaurants(id) ON DELETE CASCADE,
  full_name VARCHAR(120) NOT NULL,
  role VARCHAR(30) NOT NULL DEFAULT 'waiter'
);

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