We type a source station and a destination station.
And the application gives us the bus you can take say V333.
Now what if the their no direct bus from source to destination station , app shows the indirect route , like first go from source to mid station then mid to your final destination.
How they do that , How to map such kind of problem.
What should be the approach.
How to design a database for this kind of situation.
And if suppose you type a place name that is not directly map to their app. They show nearest station to which you buses are available.
And they also tell how much distance your actual destination is from the nearest found station.
How they calculate fare (I guess by distance).
I am thinking to make this kind of app , If somebody can provide some insight.
But can you tell me how to approach using Relational databae.
I understood It will not be efficient as number of joins and search across different tables.
But still I will like to do this using relational one for learning purpose.Anyway Its not going to deployed on any server other than my local tomcat.
I am writing here , my thought process.
I will have one "bus routes" tables where all buses routes will be stored.But I am worried as relational databases have fixed schema for each entry.
All the buses will not have same number of stops.
How I will search into database once I got user selection of source and destination stops.
Tea Addict, Oracle Java Programmer , Oracle SQL Expert , Oracle Java Web Component Developer, Oracle Web Service Developer
posted 4 years ago
I haven't given this much thought, but stops are independent of lines (because a stop can serve several lines, which is important for finding routes where the passenger will have to switch lines), so they should be modeled separately. Each stop would point to the previous stop and the next stop on one particular line. Something like this:
table stop: int stop_id, varchar stop_name
table line: int line_id, varchar line_name
table stops_of_line: int stop_line_id, int line_id, int stop_id, int previous_stop_id, int next_stop_id
"previous" and "next" is not perfect terminology because it implies a direction - which isn't really modeled here. But you get the idea. Those IDs would have N/A values for the terminal stops.