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.
You will need one table that contains your Timetable:
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?