Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
    
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 Feb 23 '11 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 Feb 23 '11 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 Feb 23 '11 at 2:52
add comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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