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:
And this is what I have come up with...
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.
SELECTs
that you will need.