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:
SELECT Service_ID FROM 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" WHERE 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).
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.