Airline reservation schema template

Tables in this data model

• airports — 4 columns
• aircraft — 4 columns
• flights — 8 columns
• fares — 5 columns
• passengers — 5 columns
• bookings — 5 columns
• tickets — 6 columns
• seat_assignments — 5 columns

How to use this ER diagram template

1. Click Open in the editor. The ER diagram opens on a new page in FlowDiagram — nothing is uploaded and there is no sign-up.
2. Rename, add or delete shapes and drag them where you want; connectors stay attached as things move.
3. Drop in vendor logos from the library — 1,160 of them, including every AWS, Azure and Google Cloud service icon — or restyle it with the colour and theme controls.
4. Prefer text? Edit the SQL DDL below and paste it in through ☰ → Import / export SQL; the finished model exports back to DDL.
5. Export to PNG, SVG or PDF, or press Ctrl+L for a link that carries the whole diagram.

SQL DDL source

The whole template is the text below. Paste it into FlowDiagram, keep it in Git next to the code it describes, or hand it to an AI assistant to extend.

CREATE TABLE airports (
  airport_id INTEGER      NOT NULL PRIMARY KEY,
  iata_code  CHAR(3)      NOT NULL,
  name       VARCHAR(150) NOT NULL,
  city       VARCHAR(100) NOT NULL
);

CREATE TABLE aircraft (
  aircraft_id   INTEGER     NOT NULL PRIMARY KEY,
  model         VARCHAR(50) NOT NULL,
  registration  VARCHAR(10) NOT NULL,
  seat_capacity INTEGER     NOT NULL
);

CREATE TABLE flights (
  flight_id         INTEGER     NOT NULL PRIMARY KEY,
  flight_number     VARCHAR(8)  NOT NULL,
  origin_id         INTEGER     NOT NULL REFERENCES airports(airport_id),
  destination_id    INTEGER     NOT NULL REFERENCES airports(airport_id),
  aircraft_id       INTEGER     NOT NULL REFERENCES aircraft(aircraft_id),
  departs_at        TIMESTAMP   NOT NULL,
  arrives_at        TIMESTAMP   NOT NULL,
  status            VARCHAR(20) NOT NULL
);

CREATE TABLE fares (
  fare_id     INTEGER       NOT NULL PRIMARY KEY,
  flight_id   INTEGER       NOT NULL REFERENCES flights(flight_id),
  cabin_class VARCHAR(20)   NOT NULL,
  fare_basis  VARCHAR(10)   NOT NULL,
  price       DECIMAL(10,2) NOT NULL
);

CREATE TABLE passengers (
  passenger_id    INTEGER      NOT NULL PRIMARY KEY,
  first_name      VARCHAR(80)  NOT NULL,
  last_name       VARCHAR(80)  NOT NULL,
  passport_number VARCHAR(20),
  date_of_birth   DATE         NOT NULL
);

CREATE TABLE bookings (
  booking_id  INTEGER       NOT NULL PRIMARY KEY,
  pnr         CHAR(6)       NOT NULL,
  contact_email VARCHAR(255) NOT NULL,
  total_price DECIMAL(12,2) NOT NULL,
  booked_at   TIMESTAMP     NOT NULL
);

CREATE TABLE tickets (
  ticket_id     INTEGER     NOT NULL PRIMARY KEY,
  booking_id    INTEGER     NOT NULL REFERENCES bookings(booking_id),
  passenger_id  INTEGER     NOT NULL REFERENCES passengers(passenger_id),
  fare_id       INTEGER     NOT NULL REFERENCES fares(fare_id),
  ticket_number VARCHAR(14) NOT NULL,
  status        VARCHAR(20) NOT NULL
);

CREATE TABLE seat_assignments (
  seat_assignment_id INTEGER    NOT NULL PRIMARY KEY,
  ticket_id          INTEGER    NOT NULL REFERENCES tickets(ticket_id),
  flight_id          INTEGER    NOT NULL REFERENCES flights(flight_id),
  seat_number        VARCHAR(4) NOT NULL,
  checked_in         BOOLEAN    NOT NULL
);

Related: Database schema diagram tool · ER diagram from SQL · All database & er models templates · All 521 templates