1

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.

3

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.

  • Wouldn't it be simpler to keep the exact time say 12:30pm instead of leg_minutes? – 1nullpointer Jun 23 '18 at 20:30
  • 1
    @1nullpointer, it would be ok if you wish to a have a single immutable route per day. But then in that case you’d probably don’t need this solution at all. – Ziggy Crueltyfree Zeitgeister Jun 25 '18 at 9:43
  • Thanks, I have a scenario where in I'll have 2- 3 kind of buses . One is Local , then Express etc. Am not sure how i could actually organize better than hardcoding immutable routes . – 1nullpointer Jun 25 '18 at 11:06
  • Also , I have currently implemented your solution . I have a Question on what is the good way to find the best routes from 2 distinct routes .(I guess it is the same thing what you refer in the last para of your solution) Say , I have a bus from A --> B , and another set of bus from B -- > C . and one more from A -->D and D --> C so, what query could I use to probably list the best route suggestions based on the current time if I want to go from A to C (Do i need to post this as a new question .Posting it here since it based on the implementation in the solution provided above ) – 1nullpointer Jun 25 '18 at 11:16
  • @Ziggy Crueltyfree Zeitgeister ,How can I efficiently find the buses from a given location at a particular time . Do I need to add leg_minutes for all the earlier stops + bus_startTime for every query . Would appreciate some help in the direction .Thanks – 1nullpointer Jun 28 '18 at 9:52
1

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.).

Your Answer

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

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