I'm developing a free app to help people know how long to wait for their next bus in a specific itinerary of my country. I'm inclined to use Python and Kivy to deploy the app on multiple platforms. I have began collecting data and have some questions about layout and design of the app.

Suppose I have the following timetable (the actual timetable is much larger, this is just a snippet):

enter image description here

Frequency can be one of 8 values:

  1. Everyday
  2. Not Saturday
  3. Only Wednesday
  4. Not Sunday or State Holiday
  5. Not weekends or State Holiday
  6. Only Saturday
  7. Only during school period (September-July only, and not weekends and not Christmas, Easter or State Holidays)
  8. Not July or August

Wheelchair is 0 (false) or 1 (true) for access.

Route can be one of 5 values:

  1. All stops.
  2. Goes straight to town X, stops
  3. Goes straight to town Y, stops
  4. Goes straight to town X then straight to Y, stops
  5. Goes straight to Y then continues to rest.

Would the ideal way to organise this be to create a base "Itinerary" class with static methods such as for fetching the next bus at the users time and location, and then derive from that a "Bus(Itinerary)" class containing the actual times of the route (vertical column), a dictionary for route and frequency, and instantiate one instance per column on application start? For some reason this seems wrong and not flexible to me.

Perhaps it would be better to keep everything in a single class and put the properties and times in a matrix and then just get the correct time and bus based on matrix row (properties and location) and column (values and times) using methods? This would work fine as long as the CSV structure remains the same, and is probably quite fast and lightweight.

Another user recommended a database.

I would like to keep and read the data from an external CSV (or JSON) instead of hardcoding it just in case the timetable changes.

I'm going to implement methods to do the following:

  • Tell the user when the next bus is coming, based on current time and location, and the desired destination. The user can ask for a bus adequate for disability, or a bus that has a route for direct destinations, when possible.
  • Notify the user when the bus is x minutes away, like an alarm, for a preset start-destination.
  • Let the user check the official timetable PDF in the app.

I have no formal education in CS hence my trouble understanding how to approach this. I would really appreciate a comprehensive answer if possible.

  • 5
    The answer to this question is just about always "put it in a proper database" and "the schema depends on how you want to query the data", but with the limited details you've given here we can't really tell you much more. Nov 27 '21 at 18:48
  • You mean like SQLite?
    – Steffan
    Nov 27 '21 at 18:59
  • If you're looking at a mobile app, that's probably your best option, yes. Nov 27 '21 at 19:12

The database system

to deploy the app on multiple platforms

I would like to keep and read the data from an external CSV (or JSON) instead of hardcoding it just in case the timetable changes.

And how do you intend to sync those CSV files across your multiple platforms and multiple users? Based on your goals, it seems you are targeting mobile usage, so that would be a painstaking process.

It makes a lot more sense here to have a universally accessible source of information, which is much easier to achieve using a public API. While this API could use a CSV file as its backed data source, parsing the same file is not going to be as performant as an actual dedicated database provider.

I can't decide this for you. I don't know your budget, scope, and performance expectation. But your intention reads to me like you're trying to take the barebones/what you can think of/simplest route, rather than having weighed multiple options and made an informed decision.

The database format

The right data format depends on the "rules" of the data. By rules, I mean what can be assumed to be fixed in stone and what cannot. Things that are fixed in stone can be made more efficient through hardcoding, but are notoriously hard to every make flexible again.

Generally speaking, it is advised to keep your data structure friendly to changes, and not set things in stone just because they happen to be the same for the current data but not necessarily for all future data that you will enter into the system.

Route is a good example of this. Right now, you list 5 options, presumably because those are the 5 different route types you're dealing with. But how sure are you that your future data is going to conform to this? What happens when another stop is added? I highly doubt that you can set these 5 options in stone for the rest of your application's lifetime, and even if you simply extend the possible values, that it will be clean or maintainable to do so for the usual size of your average public transport system.

Furthermore, when you start dealing with several different bus lines with wildly different stops, stop orders, variations on bus lines, ... your proposed single-table format would become massively unwieldy.

A database with separate tables for your data is a much more resilient solution that allows for larger data sets without groaning under the weight of an inefficient data structure.

You should really read up on database normalization. This is the core technique at play here, and it is an essential skill for any developer. It's better to learn how to do it yourself than it is to blindly adapt an answer here and then get stuck on your next project when you need to normalize a new data set.

In this case, you'd end up with three tables: Bus ("this bus"), Stop ("this location"), and BusStop (a particular bus stopping at a particular location).




This will get you most of the way there for the data set you presented.

Note also that your Route has disappeared. It has been moved into defining specific bus stops for your bus. If the bus doesn't stop at Town1, then don't add a Town1 bus stop to your bus.


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