Interview Question
Thanks your solution will work. Step 1: as per ur defination of table we will get all bus_stop_names on a specific route but what if we also want them in order bus traverse them. I think one solution for this will be, add one more column having sequence_no like source as 1, destination as N and in between bus_stops will get corresponding number.
The problem, as I understand, can be solved through using a graph structure.
If you have to use the Relational Database, you could create many-to-many relations between nodes(bus stop) and edges(with weight). Retrieve them into a standard structure (Directed graph) and then you could use standard algorithms (or methods) on the structure.
You could also use Neo4j (the Graph Database). It is easily expandable!
I assume each bus stop lies on at least 1 route. So let's say we have a table like RouteTable(BusStopName,RouteNumber). So if a new bus stop lying on n number of routes is added, we add n rows to this table.
- sdm February 26, 20101.For finding all the bus stops on a route,
select BusStopName from RouteTable where RouteNumber=?
2.For finding all the routes between bus stops s and d
select RouteNumber from RouteTable where BusStopName = s
INTERSECTION
select RouteNumber from RouteTable where BusStopName = d
If the above query returns nothing, there is no connecting direct route. In such a case,
1.you need to find out all the bus stops which lie on the routes having s.
2.Similarly find out all the bus stops which lie on the routes having d.
3.Find the common bus stops of steps 1 and 2 above along with their routes numbers for s and d.
4.The result of 3 would give you the change bus stop and the 2 routes which you would follow one after another to reach from s to d.