Train Operations Database Schema

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
  }