Bus timetable Database structure

Question

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.

Answer 1

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.

Answer 2

I've had a look at this - am going to go back to it tomorrow, but I think you're going to have to think of every route as a number of sub-routes. Route A - F is also routes A - E, A - D.... D - E and E - F.

BTW, I'm using PostgreSQL - D. Richard Hipp (the author of SQLite) has said that he uses PostgreSQL as a template for his own flavour of SQL, so it's the closest mainstream server to SQLite.

I created two tables - route and stop:

CREATE TABLE route(route_id SERIAL, route_name VARCHAR(20));

and

CREATE TABLE stop
(
  stop_id SERIAL, 
  stop_route INT, 
  stop_position INT, 
  stop_name VARCHAR(20), 
  time_to_next_stop int
);

I then populated them like this:

INSERT INTO route (route_name) VALUES('Route_1');

and

INSERT INTO stop (stop_route, stop_position, stop_name, time_to_next_stop) VALUES(1, 1, 'Stop_1',  35);
INSERT INTO stop (stop_route, stop_position, stop_name, time_to_next_stop) VALUES(1, 2, 'Stop_2',  50);
INSERT INTO stop (stop_route, stop_position, stop_name, time_to_next_stop) VALUES(1, 3, 'Stop_3',  90);
INSERT INTO stop (stop_route, stop_position, stop_name, time_to_next_stop) VALUES(1, 4, 'Stop_4', 120);
INSERT INTO stop (stop_route, stop_position, stop_name, time_to_next_stop) VALUES(1, 5, 'Stop_5',  45);
INSERT INTO stop (stop_route, stop_position, stop_name, time_to_next_stop) VALUES(1, 6, 'Stop_6',  30);

and came up with the following query:

WITH cte AS
(
   SELECT 
     r.route_name,
     st1.stop_route AS route_id, 
     st1.stop_position as stop1_pos, 
     st1.stop_name as stname1,
     st1.time_to_next_stop as st1_time, 
     st2.stop_position as stop2_pos, 
     st2.stop_name as st2_sname     
   FROM stop st1 
   LEFT JOIN stop st2
   ON st1.stop_route = st2.stop_route
   JOIN route r
   ON st1.stop_route = r.route_id
 ),
all_routes AS
(
   SELECT * FROM cte
   WHERE stop1_pos != stop2_pos
   AND stop1_pos < stop2_pos
   ORDER BY stop1_pos, stop2_pos
 )
SELECT * FROM all_routes;

The result is:

 route_name | route_id | stop1_pos | stname1 | st1_time | stop2_pos | st2_sname 
------------+----------+-----------+---------+----------+-----------+-----------
 Route_1    |        1 |         1 | Stop_1  |       35 |         2 | Stop_2
 Route_1    |        1 |         1 | Stop_1  |       35 |         3 | Stop_3
 Route_1    |        1 |         1 | Stop_1  |       35 |         4 | Stop_4
 Route_1    |        1 |         1 | Stop_1  |       35 |         5 | Stop_5
 Route_1    |        1 |         1 | Stop_1  |       35 |         6 | Stop_6
 Route_1    |        1 |         2 | Stop_2  |       50 |         3 | Stop_3
 Route_1    |        1 |         2 | Stop_2  |       50 |         4 | Stop_4
 Route_1    |        1 |         2 | Stop_2  |       50 |         5 | Stop_5
 Route_1    |        1 |         2 | Stop_2  |       50 |         6 | Stop_6
 Route_1    |        1 |         3 | Stop_3  |       90 |         4 | Stop_4
 Route_1    |        1 |         3 | Stop_3  |       90 |         5 | Stop_5
 Route_1    |        1 |         3 | Stop_3  |       90 |         6 | Stop_6
 Route_1    |        1 |         4 | Stop_4  |      120 |         5 | Stop_5
 Route_1    |        1 |         4 | Stop_4  |      120 |         6 | Stop_6
 Route_1    |        1 |         5 | Stop_5  |       45 |         6 | Stop_6
(15 rows)

I haven't been able to make more progress than that, but you may be able to do a CROSSTAB and/or use windowing/analytic functions for cumulating the times in some fashion? I'm giving the question a +1 though as I found it interesting - will look again tomorrow. You will have (n * (n - 1))/2 (= 6*5/2 in this example) "routes" where n = the number of stops (one-way).

You'll have to put in individual times anyway - since the times between stops will presumably vary by time of day (rush hour &c.).