ErdDocs

Example schemas / Restaurant Management System

Restaurant Management System ER diagram

A restaurant database splits into three zones: the front of house (dining tables and reservations, where a booking fixes a time and party size and the table is assigned only at seating), the menu (dishes linked to ingredients through a recipe — a bill of materials), and the money (orders and order lines with prices frozen at order time, for dine-in, walk-ins and takeout alike).

Your schema

Hover a table to light up its relations, click to pin. Switch to Data Dictionary for the printable reference version of this schema.

What each table does

  • dining_tables — Physical seating; the name dodges the reserved-word collision a table called "tables" invites.
  • reservations — A promise of a time and a party size; the table is assigned at seating, not at booking.
  • menu_items — The menu; active is a retirement flag, never a DELETE.
  • ingredients / recipe_items — The pantry, and the bill of materials linking each dish to what one serving consumes.
  • orders — One per visit or takeout ticket; nullable links to a table and a reservation cover walk-ins and takeout.
  • order_items — The order lines — the same header/line split as e-commerce, prices frozen.

Design decisions worth copying

  • reservations.table_id is nullable by design: a booking fixes when and how many, and pinning furniture at booking time forces refusing a party of four because "table 7" is taken while three other four-tops sit free. Assignment at seating keeps the capacity math solvable.
  • recipe_items is a bill of materials — the same junction-with-payload shape as order_items, but the payload is a recipe quantity. Closing an order can walk it to decrement ingredients.on_hand, which is how "86 the salmon" becomes a query.
  • menu_items.active instead of DELETE: past order lines reference dishes, so deleting one either orphans history or cascades it away. Retiring is an UPDATE and old receipts keep their meaning.

The SQL

PostgreSQL dialect; paste it into the tool above (it is already there), into your own database, or into a migration. Copy freely — example schemas are meant to be taken.

CREATE TABLE dining_tables (
    id     bigserial   PRIMARY KEY,
    label  varchar(10) NOT NULL UNIQUE,
    seats  int         NOT NULL CHECK (seats > 0)
);
COMMENT ON TABLE dining_tables IS 'Named dining_tables because "tables" fights the SQL vocabulary';

CREATE TABLE reservations (
    id          bigserial    PRIMARY KEY,
    table_id    bigint       REFERENCES dining_tables(id),
    guest_name  varchar(120) NOT NULL,
    phone       varchar(40),
    party_size  int          NOT NULL CHECK (party_size > 0),
    starts_at   timestamptz  NOT NULL,
    status      varchar(20)  NOT NULL DEFAULT 'booked'
);
COMMENT ON COLUMN reservations.table_id IS 'NULL until seated: a booking fixes time and size, not furniture';
COMMENT ON COLUMN reservations.status IS 'booked -> seated -> done, or no_show / cancelled';

CREATE TABLE menu_items (
    id        bigserial    PRIMARY KEY,
    name      varchar(120) NOT NULL,
    category  varchar(40)  NOT NULL,
    price     numeric(8,2) NOT NULL,
    active    boolean      NOT NULL DEFAULT true
);
COMMENT ON COLUMN menu_items.active IS 'Dishes retire instead of being deleted, or old orders lose meaning';

CREATE TABLE ingredients (
    id       bigserial     PRIMARY KEY,
    name     varchar(80)   NOT NULL UNIQUE,
    unit     varchar(12)   NOT NULL,
    on_hand  numeric(10,3) NOT NULL DEFAULT 0
);

CREATE TABLE recipe_items (
    menu_item_id   bigint       NOT NULL REFERENCES menu_items(id),
    ingredient_id  bigint       NOT NULL REFERENCES ingredients(id),
    qty_per_serving numeric(8,3) NOT NULL,
    PRIMARY KEY (menu_item_id, ingredient_id)
);
COMMENT ON TABLE recipe_items IS 'The bill of materials: what one serving consumes';

CREATE TABLE orders (
    id              bigserial   PRIMARY KEY,
    table_id        bigint      REFERENCES dining_tables(id),
    reservation_id  bigint      REFERENCES reservations(id),
    opened_at       timestamptz NOT NULL DEFAULT now(),
    closed_at       timestamptz
);
COMMENT ON COLUMN orders.table_id IS 'NULL for takeout';
COMMENT ON COLUMN orders.reservation_id IS 'NULL for walk-ins';

CREATE TABLE order_items (
    order_id      bigint       NOT NULL REFERENCES orders(id),
    menu_item_id  bigint       NOT NULL REFERENCES menu_items(id),
    qty           int          NOT NULL CHECK (qty > 0),
    unit_price    numeric(8,2) NOT NULL,
    PRIMARY KEY (order_id, menu_item_id)
);
COMMENT ON COLUMN order_items.unit_price IS 'Frozen at order time, like any invoice line';

FAQ

How do I find a free table for six at 19:00?

With a query, not a column: tables whose seats >= 6 that have no reservation overlapping the requested time slot. It is the same interval-overlap logic hotel booking uses at day granularity, one level finer.

Where do modifiers like "no onions" go?

Free text fits in a note column on order_items. Structured, priced modifiers ("extra cheese +1.50") need an order_item_options table — a junction one level deeper, priced like a miniature order line.

More example schemas