0

I am in the process of converting a bus timetable to a database structure. I am building a free mobile app for a specific itinerary in my country that will:

  • let users know when their next bus is due to arrive,
  • tell them how long it will take to arrive at their destination,
  • alert them x minutes before their planned trip, etc...

The app will be implemented in Python with Kivy, and a SQLite database. It will be able to run offline.

This is the original timetable:

Original

And this is what I have come up with...

enter image description here

Code:

Table stop as S {
  stop_id int [pk, increment] // auto-increment
  name varchar
  coordinates_id int [ref: - C.coordinates_id]
}

Table coordinates as C {
  coordinates_id int
  latitude float
  longitude float
}

Table user as U {
  user_id int [pk, increment]
  name varchar
  tax_number int
  buspass_id int [ref: - B.buspass_id]
}

Table buspass as B {
  buspass_id int
  start_stop int [ref: - S.stop_id]
  end_stop int [ref: - S.stop_id]
  validity datetime
}

Table route as R {
  route_id int [pk, increment]
  disabled boolean
  route_frequency enum
  route_direction enum
  route_type enum
 }

Table timetable as TT {
  timetable_id int [pk, increment]
  route_id int [ref: > R.route_id] 
  stop_id int [ref: > S.stop_id]
  time timestamp
}

//----------------------------------------------//
Enum route_direction {
  coimbra_serpins
  serpins_coimbra
}

Enum route_frequency {
  daily
  not_saturday
  wednesday
  not_sunday_or_holidays
  not_weekend_or_holidays
  saturday
  school [note: "not weekends, holidays or July and August"]
  not_july_or_august
}

Enum route_type {
  direct_miranda
  direct_lousa
  semidirect_miranda_lousa
  semidirect_lousa_serpins
}

I will need to do a bunch of queries such as:

  • Find the next departure based on the current time and departure location, and arrival location and arrival time.
  • List all stops of the ideal route, with arrival times for each.
  • Know if bus pass is almost out of data, i.e. when it was purchased.

I would like to know if this structure seems appropriate for my end goal.

20
  • 1
    What will the lat/lng be used for? Is the "frequency" constant? Or does it change for nights, weekends, holidays? Try to write the SELECTs that you will need.
    – Rick James
    Nov 28, 2021 at 6:46
  • Lat long is for storing geolocation of bus stops, so I can integrate Google Maps API later on. Frequency is constant.
    – Steffan
    Nov 28, 2021 at 9:00
  • 1
    Time of timetable is a timestamp. A timestamp contains a date. Do you plan to store routes for a number of months or years ahead? Over here, routes depend on the day of the week, eg. mon- to friday, saturday, sundays and holidays, not on the exact date. They'd have to renew the charts in the bus-stops. Not that they couldn't do that, they change the ads every so often. Nov 28, 2021 at 9:58
  • 1
    Hi, and welcome to dba.se! Please don't use images here for the reasons outlined in this link. Please put in the referential constraints - also, please show us what you have attempted yourself - help us to help you. Nov 28, 2021 at 10:04
  • 1
    @Steffan postgresql.org/docs/13/datatype-datetime.html time with/without timezone? Nov 28, 2021 at 11:04

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.