So far, I've assumed that the data being stored (timings) is static. But in reality, trains often run under special circumstances. Where I stay, I often see cases like:
- Train A does not stop at X station
- Train B has its timing changed to ### until DD/MM/YY
- Train C will only run until station M until DD/MM/YY
- and so on...
There cannot be a generic solution that can solve all such cases in one place. So let me take each one and see how to solve it. If patterns emerge, awesome! If not, I have to look into how to solve such cases in the easiest way possible.
If train A does not stop at a particular station, it's timing value for that station can be set to NULL, which is a special notation meaning no value. When creating the timings for a train, this particular condition should be checked, and the value set to NULL where applicable.
add_train(train): no_stop_stations = select station from special_cases where train=train and condition=no_stop # ... rest of the code for station in station_list: if station in no_stop_stations: timing.time = NULL
When the train's timings have changed temporarily, the old timings can be saved and the new ones applied.
change_time_temporary( train, new_time, until_date): # save the old timing to a temporary record save_to_temp(train.train_no, train.timing) # delete all timings for this train delete timings where train=train # save new timings train.timings = new_timeadd_train(train)
The temporary record can be a file, or a separate table reserved for such instances. Any form of persistence where the old train timings can be saved and later restored can work. Restoring is pretty much the same as saving, where the old values are retrieved, the record is deleted from the temp table. The timings are deleted for this particular train, and then added again with the old values.
For cases, where a train will run only until a particular station until DD/MM/YY (it won't run till its assigned destination), the case is similar, where instead of saving timing to a temp table, we save the original destination.
change_destination_temporary( train, new_destination, until_date): # save the old destination to a temporary record save_to_temp(train.train_no, train.destination) # delete all timings and add the train again delete all timings where train=train # save train with new destination train.destination = new_destination # get stations based on this new destination add_train(train)
The real problem here would be applying these cases to the data model. One way is to hard code such instances into the code itself. Another way is to expose a set of API parameters which would allow building queries for such special cases. This requires significant work, but allows a large variety in the special cases that can be handled.
A set of choices can be provided to build the queries, such as selecting a train_no in the first column and one of these in the second:
- DOES NOT STOP -> next field would be a station selector
- TIMING CHANGED -> next field would be a time field
- RUNS UNTIL -> next field would be a station selector
It is possible to store both the station id and time values in a single field in cases where the time field values are stored as integers (seconds since epoch, for e.g.). If not, they can be stored as binary objects, or blobs, and cast to the correct type when required. A fourth column, called ORIGINAL_VALUE can also be added to the table to store the original values. This keeps all associated values in one place, and does not require temporary tables.