I am designing a bus timetable using SQL. Each bus route has multiple stops, do I need a different table for each route?

Question

I am trying to come up with the most efficient database as possible. My bus routes all have about 10 stops. The bus starts at number one until it reaches the 10th stop, then it comes back again. This cycle happens 3 times a day.

I am really stuck as to how I can efficiently generate the times for the buses and where I should store the stops. If I put all the stops in one field and the times in another, the database won't be very dynamic.

If I store all the stops one by one in a column and then the times in another column, there will be a lot of repeating happening further down as one stop has multiple times.

Maybe I am missing something, I've only just started learning SQL and this is a task we have been set.

Thanks in advance.

Answer 1

You will need one table that contains your Timetable:

• Route ID
• Stop ID
• Time
• Possibly other fields as needed (direction, sequence #'s, Block #, etc)

I would recommend creating separate tables Bus Stop (to store stop names, lat/longs, etc) and Route (to store route name, first stop, last stop, direction, etc).

You are probably aware of this already, but bus scheduling can get complicated very quickly. For example:

• You may need to designate certain stops as "Time Points" which show up in the printed schedules

• Each route may have multiple variations. For example, some versions may start or end at a different bus stop

• The schedule will probably be different on Saturday and Sunday, and most agencies change their schedules quarterly

You may need to consider some of these cases, and build them into your schema.

Does that help?

Answer 2

On a (very rough) 1st pass, I would keep the bus route times in a table like this:

RouteID StartingLocationID  EndingLocationID TravelTime

Also I would keep a table of stops such as:

StopID Address City etc... (whatever other information you need about each location)

For the routes themselves I would store:

RouteID StartingLocationID RouteStartTime

Obviously you should tailor this to your own needs, but this should give you a place to start.

Answer 3

Here's just one (of the many) ways to do this:

It sounds like you probably want to have a routes table, which describes each route, and has a start time.

Then, a stops table with descriptions and wait times for the bus at each stop.

A stopDistanceMapping table would describe the distance between two stops, and the drive time between them.

Finally, your routeMap table will link individual routes with a list of stops. You can then fill your routes table distance and time in using the wait time from each individual stop, and the times/distances from stopDistanceMapping.

Good luck!