We have bus station data, and we want to build an application which will provide the line/multiple line for a given start station and the end station.
For example user try to get a bus line suggestion from station1 to station2.
If there is a bus line which can cover both
station2, this line should be returned. The result may look like this:
Step1: station1 -- station2
If there is no direct bus line between station1 and station2, then the application should try to find the exchange plan,for example, the result may look like this:
Step1: station1 -- exchangestation Step2: exchangestation -- station2
Now we have the data, but we do not know how to design the data model in the database, how to create the schema to make the query efficient?
For example, I have four bus lines(actually two) each with a different color:
l1: A-B-C-D l2: D-B-A (in fact, the l2 is the reverse of l1 except the l2 will skip station `C`. l3: E-B-D-F l4: F-D-B-E(reverse of l3)
Now if we want to save these information in the database to query the bus line plan for given one station to another, then how many tables do we need, and what should be put into each table ?