designing data models for trains, timings, and station
published: (updated: )
by Harshvardhan J. Pandit
is part of: Pune-Lonavala Train Schedule App
automation python script trains
Train Stations
The train stations are setup as follows:
- Pune
- Shivajinagar
- Khadki
- Dapodi
- Kasarwadi
- Pimpri
- Chinchwad
- Akurdi
- Dehuroad
- Begdewadi
- Ghorawadi
- Talegaon
- Vadgaon
- Kanhe
- Kamshet
- Malavali
- Lonavala
Each station has an id number (the same as the index in above list) that
is used as its unique identifier. Storing and retrieving data in a
database is easier with primary keys, which are like the unique
identifiers for each record. If the user doesn't supply one, the
database creates one automatically. In this case, explicitly setting the
ID is beneficial as it allows nifty queries such as
select stations where id<=15;
would give all stations from Pune to
Kamshet.
Each station has three properties:
- Name
- Time taken to reach this station from the previous one: time_from_prev
- Time taken to reach the next station from this one: time_to_next
Based on these timings (next and previous) we can calculate the train timing once we know three things:
- Where does it start from? (initial station)
- Where does it go to? (destination)
- When does it leave the initial station? (timing)
Train
A train has five properties:
- train_id: a unique integer used to identify the train, like the station_id
- train_no: each train has a number used to identify the train (given by the Railways Department)
- initial station: station_start
- destination: station_end
- timing for leaving the initial station: timing
The two stations, initial and destination refer to the Train Station, whereas the timing is a Time object.
Storing train runs
Each train run consists of the train leaving the initial station at the specified time towards its destination. We can calculate the scheduled stops at all stations based on the station's next and previous timing properties. For eg. A train leaves Pune (station = 1) at exactly 12pm (noon) for Lonavala (station = 17). Then we can have:
station_list = [
stations where id >= Pune.id
and id <= Lonavala.id ]
( order by id )
train = 12:00
time_to_next = 0
for station in station_list:
time = time + time_to_next
train stops at <station> at <time>
time_to_next = station.time_to_next
This generates the train's time runs from Pune to Lonavala. Using this approach to generate the timings every time a request comes in is not efficient. Instead, we can use this to calculate the timings for a train when it is added, and save it to database. So we have a separate table for Timings that contains: * timings: Time Field * train: reference to Train * station: reference to Station
This approach makes sense, since we can get the following frequently required things very easily:
- get timings for train A:
select timings where train = A
- get all trains for station B:
select timings where station = B
- get all trains for station B going towards C:
select timings where station = B and train in [ trains going up (pune) or down (lonavala) ]