Development of a railway inquiry system, simulation of trains, stations and stops?

Question

For example, if I have two trains, X and Y, and they move:

TRIP ......... STATIONS

• Train-X: goes through station-A; Station-B; Station-C; Station-D
• Train-Y: goes through station-B; Station-X; Station-D; Station-y

How can I put this information in a database so that if a passenger asks about What starts with trains like Station-B? and What cooks End as Station-D? then both Train-X and Train-Y come to the result.

Answer 1

I would say that for this work you need to have three tables.

Station : station identifier, name, etc.

Service : service identifier, operator, number of train cars, etc.

Service_Stop : service identifier, stop number, station identifier.

You can then find the services that stop at station-B and then at station-D using the following query:

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' 

Answer 2

I would use five tables :.

 Train: train_id, name # eg "The UnionT522" Station: station_id, name # eg "Eggles Place" Route: route_id, route_name # eg "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 # eg which train (above) is going on which route (above). 

So, RouteStation will have things started or ended at this station for a given route.
TrainRoute will have information on which route the train travels.