Example schemas / Restaurant Management System

Restaurant Management System ER diagram

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

Below, ErdDocs draws this schema from its SQL, every table and every relation. The Data Dictionary tab has the printable reference, complete and free on this page. The SQL is further down.

Your schema

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';

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';

Your diagram appears here.

Hover a table to light up its relations, and click to pin them.

What each table does

dining_tablesPhysical seating; the name dodges the reserved-word collision a table called "tables" invites.
reservationsA promise of a time and a party size. The table is assigned at seating, not at booking.
menu_itemsThe menu. The active column is a retirement flag, never a DELETE.
ingredients / recipe_itemsThe pantry, and the bill of materials linking each dish to what one serving consumes.
ordersOne per visit or takeout ticket. Nullable links to a table and a reservation cover walk-ins and takeout.
order_itemsThe order lines, with frozen prices. The same header/line split as e-commerce.

Design decisions worth copying

reservations.table_id is nullable by design, because a booking fixes when and how many. Pinning furniture at booking time forces you to refuse 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. It has 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.
Dishes retire through menu_items.active instead of a 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. You are free to copy it.

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: look for 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. That is a junction one level deeper, priced like a miniature order line.

More example schemas

Northwind Database ER diagram
AdventureWorks Database ER diagram
All example schemas
Data dictionary generator: the documentation side of the same tool.