CODEWITHSUNDEEP
mysqldatabasetimetable
ivanhoe_gg
ivanhoe_gg

Reputation: 81

How to store a lot of different timetables in MYSQL?

I need help about how to model a database. I need to store the timetable for each transport public line. Lets see what we have...

The aim of the program that I'm developing is to check for errors in the official timetables. Each bus has a tracking GPS device that sends its position to a database every 10 seconds. So I must check the hour of the reports whose coordinates are close to the coordinates of one of the stops and compare that time with the official time, and in case there is a big difference, create a row in other table STATISTICS reporting the issue.

Anyway, this was just for the context. The truth is that I don't have any clue about how to store it in an efficient way.

I thought about creating a table with the Stops: STOP_ID (PK) - NAME - LAT - LON - LINE - TIMETABLE

Where timetable would be an array containing all the times serialized for that stop [5:03,5:25,5:50,6:12,...].

Although I think this is not a good solution, I can't think about a better approach.

Maybe I could create a table for the stops, and other for timetables, but what would be the columns for timetables? I have so many variables... if it's weekly, saturday or holiday, a lot of hours, minutes... and all different for each stop.

Could you share any thoughts about how to face this problem? Thank you very much!!

Upvotes: 0

Views: 1370

Answers (1)

O. Jones
O. Jones

Reputation: 108839

As Simon mentioned, you are starting a big project.

Suggestion: Read up on the various normal forms for relational DBMSs; this will give you some helpful background if you don't have it.

What are your entities (tables)? Bus lines (consider the outbound trip and return trip to be two different lines). Stations on those lines, ordered. Trips (e.g. 106 bus leaves central station at 05:22, another trip at 05:42, etc). Scheduled-stops GPS observations.

Here are possible tables and columns:

 Busline table:   one row for each busline.   
   Busline  e.g. 106-outbound or 108-inbound   (pk)
   Description

 Station table:   one row for each bus stop, including ends of trips
   Busline        part of pk, fk to Busline  e.g. 106
   Stationid      part of pk  kf to Station 
   Description    e.g. Second Avenue Eastbound at Houston Street
   lat
   long

 Trip table: One row for each bus trip.
   Tripid   pk
   Busline  fk to Busline
   Description  e.g.   05:22 trip Central Station to University Park

 Schedule table:  one row for each scheduled time for each trip at each stop 
   Scheduleid  pk ... ascending serial number.
   Busline     fk to Station
   Stationid   fk to Station
   Tripid      fk to Trip
   Time

 Observation table  a row for each of your GPS readings
   Observationid  pk  ... ascending serial number
   Busline if you know it   fk to Busline
   Tripid if you have it    fk to Trip
   Time
   Lat
   Long

My advice with RDBMS design is to avoid serializing multiple items of data into single DBMS columns. That's why I have suggested the Schedule table.

Once you figure out how to load your Busline, Station, Trip, and Schedule tables, and you've loaded your observations into the Observation table, it will be an interesting exercise to correlate your observations with your schedules.

Be careful! You may embarrass your municipal transport department! :-)

Upvotes: 2

Related Questions