I am developing a bus schedule using SQL. There are several stops on each bus route, do I need a table for each route?

Question

I am trying to find the most efficient database. My bus routes have only about 10 stops. The bus starts from the first place, until it reaches the 10th stop, and then returns. This cycle occurs 3 times a day.

I really got stuck on how I can efficiently generate time for buses and where I need to store stops. If I put all the stops in one field and the time in another, the database will not be very dynamic.

If I save all the stops one after another in a column, and then the time in another column, there will be many repetitions occurring further, since one stop has several times.

Maybe I missed something, I just started to learn SQL, and this is the task that we set.

Thanks in advance.

Answer 1

You will need one table containing your Timetable :

• Route id
• Stop id
• Time
• Other fields may be needed (direction, sequence #, block #, etc.)

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

You probably already know this, but bus planning can get very complicated quickly. For instance:

• You may need to designate certain stops as “time points” that appear on printed schedules.

• Each route can have several options. For example, some versions may start or end at another bus stop.

• Schedules are likely to differ on Saturday and Sunday, and most agencies change their schedules quarterly.

You may need to consider some of these cases and create them in your schema.

Does it help?

Answer 2

Here is just one (out of many) ways to do this:

It looks like you probably want to have a routes table that describes each route and has a start time.

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

Table

A stopDistanceMapping describes the distance between two stops and the time between them.

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

Good luck

Answer 3

On the (very rough) 1st pass, I would save the bus route time in the table as follows:

 RouteID StartingLocationID EndingLocationID TravelTime 

I would also save the table of stops, for example:

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

For the routes themselves, I would save:

 RouteID StartingLocationID RouteStartTime 

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