I am interested in what a MySQL database schema would look like for a train timetable.
Generally this is presented in table format as the final result.
Train No. 11111 22222 11111 Train Day Mo-Fr Sat Sun Station A d 06.00 07.00 07.00 Station B d 06.10 07.10 Station C d 06.20 07.15 Station D a 06.30 07.40 Station D d 06.35 07.25 Station E d 06.45 07.45
So each train has a train number, but that isn't unique as the number will be duplicated for a Monday-Friday to a Saturday and Sunday. Then a train will stop at a number of station, but not all, and sometimes the station needs an arrival (a) and departure (d) time, or one or the other.
Stations can be ordered by their distance.
So far I am thinking I need tables for:
- Stations (ID, name, distance, line)
- Train (ID, train no, Day Operation, details)
- Train run (Train.ID, Station.ID, arr/dep, time, notes)
Is this normalised enough?
- Should say train 11111 for Mon-Fri be just one entry with lots of combinations of Day Operation, ie 7 columns (M, T, W, Th, F, S, Su) ?
- Shouldn't it also handle a one off train that runs on say just one date and doesn't repeat weekly?