For instance, basically have two trains, X and Y plus they travel:


  • Train-X : Goes Via Station-A Station-B Station-C Station-D
  • Train-Y : Goes Via Station-B Station-X Station-D Station-Y

Wouldso would Time passes about putting these details inside a database to ensure that if your passenger asked about What trains start as Station-B? and What trains Finish as Station-D? then both Train-X and Train-Y should are available in the end result.

I'd say you must have three tables to make this happen.

Station: station ID, title, etc.

Service: service ID, operator, quantity of train cars possibly, etc.

Service_Stop: service ID, stop number, station ID.

After that you can find services that visit Station-B and subsequently at Station-D utilizing a query something similar to the next:

    Station AS Start_Station
    JOIN Service_Stop AS Start_Stop ON
        Start_Station.Station_ID = Start_Stop.Station_ID
    JOIN Service_Stop AS End_Stop ON
        Start_Stop.Service_ID = End_Stop.Service_ID AND
        Start_Stop.Stop_Number < End_Stop.Stop_Number
    JOIN Station AS End_Station ON
        End_Stop.Station_ID = End_Station.Station_ID AND
        End_Station.Name = "Station-D"
    Start_Station.Name = 'Station-B'

I'd use five tables:.

Train: train_id, name   # e.g "The UnionT522"
Station: station_id, name # e.g. "Eggles Place"
Route: route_id, route_name # e.g. "Afternoon special at 4pm" 
RouteStation: route_station_id, route_id, station_id, route_order, begin_flag, end_flag
TrainRoute: train_route_id, train_id, route_id  # e.g. which train (above) is going on which route (above).

So RouteStation might have whether things started or ended in a given station for any given route.
TrainRoute might have details about which route a train takes.