7

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?
Improve this question
3
  • When you're modeling the data, don't look at what the input format is -- ask what questions you want to be able to ask of the data. Without knowing what sort of questions you'll have, we can't figure out the queries that we'd need to run against the data structures, and so if it was a good design or not.
    – Joe
    Commented Feb 23, 2011 at 1:21
  • Oh ... and I'd likely look to see how the original Palm calendar program handled single vs. repeating events; it seemed to be able to handle just about anything you could throw at it in terms of event schedules; much more luck that I've had with iCalendar / vCalendar / hCalendar.
    – Joe
    Commented Feb 23, 2011 at 1:24
  • The example given is the output. The sort of queries I would run would be to output: train times at station X, timetable for train Y or all train timetables (example given). Ie a filter effect.
    – Vicsig
    Commented Feb 23, 2011 at 2:52

2 Answers 2

Reset to default
7

I would strongly consider explicitly storing all the days a particular schedule actually runs on. This would give a structure looking something like this:

enter image description here

By doing so, you'll make it much easier to answer questions such as "What are all the trains going to station X on 1 Oct?". It'll also makes "temporary gaps" when trains aren't running (e.g. Christmas day) possible to identify. A one-off train is now simply one with only one entry in SCHEDULE_DAYS.

As the schedule can be different on weekends to weekdays, I think it's better to have separate rows for each day. This allows linking different schedules for every day of the week, should you ever need to do this.

Improve this answer
5
  • What if you want to introduce a an aspect of direction? The schedule table does say from which station the train comes from
    – theTypan
    Commented Apr 3, 2020 at 6:23
  • 1
    You can infer the direction from the arrival/departure times for the stations in a schedule
    – Chris Saxon
    Commented Apr 3, 2020 at 15:24
  • I dont understand the usage of schedule days table Chris. For scheduling purpose Can we use only station table, Train table, Schedule Stations...But in last one you have to include date and train id..
    – Prageeth Liyanage
    Commented Sep 5, 2023 at 8:59
  • @PrageethLiyanage schedule_days stores a row for every day a schedule runs on; you could possibly combine train schedules and schedule days. It's still worth having a table with a row/day/schedule independent of stations to make queries/day easier though.
    – Chris Saxon
    Commented Sep 5, 2023 at 15:24
  • @ChrisSaxon What is the difference between the operating day and date on Schedule Day table? Trains are categories as Daily trains, Not On Sunday, Not on Saturday like wise...How we add the records to the tables? Thank you
    – Prageeth Liyanage
    Commented Dec 19, 2023 at 8:33
1

You can handle the one off runs by having date fields for first run, and termination (first date it no longer runs). This would also allow you to handle schedule changes. First run date becomes part of the primary key. You may want a surrogate primary key on this table for the relationship table.

I would consider splitting the days of the week into Seven separate indicator fields. This gives you the flexibility to handle schedules that only run certain days of the week. The train-station relationship could be reduced to (train, stations, type (arrival/departure) time of day). Notes could go into a separate relationship depending on their density.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.