CloudSketch AI Logo
FeaturesTemplatesPricingEnterpriseAboutContactLog in
🌙☀️
Log inStart free
Home/Templates/Railways & Public Transport

🚆 Railways & Public Transport · Database ERD

Train Operations Database Schema

Tables for train operations: trains, routes, stations, scheduled stops, coaches and daily running with actual times.

More Railways & Public Transport templates

Opening a template is free and never counts toward your daily diagrams. Asking the AI to change it counts as one.

Drawing diagram…

What this diagram shows

  • The schedule and the actual running are stored separately
  • Each run records actual arrival and departure per station
  • Coach composition can change per run

Prompt used

Train operations database: trains run on routes; a route has an ordered list of stations with scheduled arrival and departure (schedule stops). Each day's train run records actual arrival and departure at each station (run stops) and its coach composition.

Mermaid code
erDiagram
  ROUTE ||--|{ SCHEDULE_STOP : "has stops"
  STATION ||--o{ SCHEDULE_STOP : "appears in"
  TRAIN ||--|| ROUTE : "runs on"
  TRAIN ||--o{ TRAIN_RUN : "runs as"
  TRAIN_RUN ||--|{ RUN_STOP : records
  STATION ||--o{ RUN_STOP : "passed at"
  TRAIN_RUN ||--|{ RUN_COACH : "formed of"
  COACH ||--o{ RUN_COACH : "used in"
  ROUTE {
    int id PK
    string name
  }
  STATION {
    int id PK
    string code
    string name
  }
  SCHEDULE_STOP {
    int id PK
    int route_id FK
    int station_id FK
    int sequence
    time sched_arrival
    time sched_departure
  }
  TRAIN {
    int id PK
    string number
    string name
    int route_id FK
  }
  TRAIN_RUN {
    int id PK
    int train_id FK
    date run_date
    string status
  }
  RUN_STOP {
    int id PK
    int run_id FK
    int station_id FK
    datetime actual_arrival
    datetime actual_departure
  }
  COACH {
    int id PK
    string coach_number
    string class
  }
  RUN_COACH {
    int id PK
    int run_id FK
    int coach_id FK
    int position
  }

Inside this diagram

An entity relationship diagram with 8 tables, 31 columns and 8 relationships.

Tables and their columns

  • ROUTE: id (PK), name
  • SCHEDULE_STOP: id (PK), route_id (FK), station_id (FK), sequence, sched_arrival, sched_departure
  • STATION: id (PK), code, name
  • TRAIN: id (PK), number, name, route_id (FK)
  • TRAIN_RUN: id (PK), train_id (FK), run_date, status
  • RUN_STOP: id (PK), run_id (FK), station_id (FK), actual_arrival, actual_departure
  • RUN_COACH: id (PK), run_id (FK), coach_id (FK), position
  • COACH: id (PK), coach_number, class

Relationships

  • ROUTE has stops SCHEDULE_STOP: each ROUTE has one or more SCHEDULE_STOP, each SCHEDULE_STOP belongs to exactly one ROUTE
  • STATION appears in SCHEDULE_STOP: each STATION has zero or more SCHEDULE_STOP, each SCHEDULE_STOP belongs to exactly one STATION
  • TRAIN runs on ROUTE: each TRAIN has exactly one ROUTE, each ROUTE belongs to exactly one TRAIN
  • TRAIN runs as TRAIN_RUN: each TRAIN has zero or more TRAIN_RUN, each TRAIN_RUN belongs to exactly one TRAIN
  • TRAIN_RUN records RUN_STOP: each TRAIN_RUN has one or more RUN_STOP, each RUN_STOP belongs to exactly one TRAIN_RUN
  • STATION passed at RUN_STOP: each STATION has zero or more RUN_STOP, each RUN_STOP belongs to exactly one STATION
  • TRAIN_RUN formed of RUN_COACH: each TRAIN_RUN has one or more RUN_COACH, each RUN_COACH belongs to exactly one TRAIN_RUN
  • COACH used in RUN_COACH: each COACH has zero or more RUN_COACH, each RUN_COACH belongs to exactly one COACH

Make your own diagram like this

Describe your own system in plain English and the AI ER diagram generator draws it in seconds, in the same style as this template.

Related templates

C4 ArchitectureProRailways & Public Transport

Train Ticket Reservation System

A railway reservation system in the style of IRCTC: seat inventory per train and class, waitlists, Tatkal quota, payments and e-tickets.

State MachineRailways & Public Transport

Train Ticket (PNR) Status

The states of a train ticket from booking to journey, including RAC and waitlist confirmation and cancellation.

SequenceRailways & Public Transport

Metro Smart Card Fare Collection

How a metro fare is charged with a smart card or bank card: tap in at the entry gate, tap out at the exit, fare by distance and balance update.

Data FlowRailways & Public Transport

Live Bus Tracking and Arrival Times

How a city bus app shows live bus positions and arrival times: GPS devices on buses, a prediction model and updates to apps and stop displays.

NetworkRailways & Public Transport

Railway Signalling and Train Control Network

How a modern railway signalling network is connected: interlocking at stations, track circuits, train control radio and the central traffic control centre.

C4 ArchitectureProRailways & Public Transport

Multimodal Journey Planner

A journey planner that combines metro, bus, suburban train and last-mile options into one trip plan with a single ticket.