database

Tables (9)

bus_routes

  • route_id — VARCHAR(20) — Primary key
  • bus_id — VARCHAR(20) — NOT NULL

bus_stations

  • station_id — VARCHAR(20) — Primary key
  • station_name — VARCHAR(100)
  • gps_x — DOUBLE
  • gps_y — DOUBLE

drivers

  • driver_id — INT — Primary key
  • name — VARCHAR(100)
  • username — VARCHAR(100)
  • password — VARCHAR(100)
  • email — VARCHAR(100)
  • license_number — VARCHAR(50)

favorites

  • id — INT
  • passenger_id — INT
  • route_id — VARCHAR(20)
  • direction — VARCHAR(100)
  • start_station — VARCHAR(20)
  • end_station — VARCHAR(20)
  • added_at — DATETIME

passengers

  • passenger_id — INT
  • name — VARCHAR(100)
  • username — VARCHAR(100)
  • password — VARCHAR(100)
  • email — VARCHAR(100)

reservations

  • id — INT
  • passenger_id — INT
  • vehicle_id — VARCHAR(20)
  • route_id — VARCHAR(20)
  • start_station — VARCHAR(20)
  • end_station — VARCHAR(20)
  • reserved_at — DATETIME
  • status — VARCHAR(50)

route_stations

  • id — INT — Primary key
  • station_id — VARCHAR(20)
  • station_order — INT
  • direction — VARCHAR(100)
  • route_id — VARCHAR(20)

usage_history

  • id — INT
  • passenger_id — INT
  • vehicle_id — VARCHAR(20)
  • start_station — VARCHAR(20)
  • end_station — VARCHAR(20)
  • reserved_at — DATETIME
  • status — VARCHAR(50)

vehicles

  • vehicle_id — VARCHAR(20)
  • bus_id — VARCHAR(20) — NOT NULL
  • plate_number — VARCHAR(20)