Bus timetable Database structure

database-designsqlite

I am creating a database for bus timetable in android app. I have created four tables: stops, route, detail_route and schedule. columns in each table are as follows.

Database structure

route (id, name)
stops (id, route_id, name)
detail_route (route_id, stop_id, time, stop_order) here time means: time require to reach to that stop
schedule (source_stop_id, destination_stop_id, route_id, start_time)

Now I have a problem here. User can select any source and any destination, but in the database I am only storing data of bus about source, destination and route.

Ex:
Here Bus route is from Point A to E :

A > B > C > D > E

I will store this entry in schedule table as 

source_stop_id : A

destination_stop_id : E
route_id : 1
start_time : 9:15 AM

now if user select source as B and Destination as D. There is no bus available from B to D according to my database. Here same bus should be shown because it is on same route. I don't know how to do it. please suggest.

Best Answer

You'll need to redefine your database to store that information. An example is the following:

CREATE TABLE routes (
  route_id int PRIMARY KEY,
  route_name varchar(200)
  );

CREATE TABLE stops (
  stop_id int PRIMARY KEY,
  stop_name varchar(200)
  );

CREATE TABLE route_details (
  route_id int,
  stop_number int, -- starting from 1
  stop_id int,
  leg_minutes int, -- minutes from the previous stop to this one (0 for stop_number=1)
  FOREIGN KEY (route_id) REFERENCES routes (route_id),
  FOREIGN KEY (stop_id) REFERENCES stops (stop_id),
  PRIMARY KEY (route_id, stop_number)
  );

CREATE TABLE schedules ( 
  route_id int,
  weekend bool,
  start_time varchar(8)
  );

-- Query which route goes from B to E
SELECT r.*
FROM routes r
JOIN route_details rs ON (rs.route_id = r.route_id)
JOIN stops ss ON (ss.stop_id = rs.stop_id)
JOIN route_details re ON (re.route_id = r.route_id)
JOIN stops se ON (se.stop_id = re.stop_id)
WHERE ss.name = 'B' AND se.name = 'E' AND rs.stop_number < re.stop_number;

When designing a database, I find it convenient sticking to a single naming convention, either plurals or singulars (i.e. routes and stops, or route and stop). Here I opted for all plurals. Also, suffixes work better than prefixes for listing tables alphabetically (it doesn't really matter in this case because there are so few tables, but it still is good practice).

This design allows you to have the same stop shared in different routes. Note I've included a weekend in schedules. This was just a suggestion, since sometimes bus tables have different schedules for weekdays and weekends.

The query would be significantly more complex for finding a route from a stop to another one using two routes, but it can be done. It also allows you easily calculate travel time, and stop times at subsequent bus stops.

Related Question