I am designing an application of bus reservation system.
I am stuck with database design of the application. Hope you people can help me on this.
Each bus have unique ID (say BUS NO), source & destination.
Now let say for
Bus B123 source is A and destination is X and in between it stops at C,Z,N,H,K also.
Bus B456 source is C and destination is Y and in between it stops at Z,N,K,P,Q,R,T,I,D also.
Bus B789 source is G and destination is S and in between it stops at N,H,K,D,M also.
Now if user search for bus from
C to H it should give B123
N to K it should give B123, B456, B789
K to D it should give B456, B789
Again if he search for Bus from
H to C it should not give B123 (i.e the bus goes only from C to H and not from H to C)
The fact that need to consider here is the sequence.
I can't keep all stations in sequence as admin may want to update the route(adding or deletion of station) in future.
I hope you have understood what I am planning to design. Please help me with this design.
I have to create this design in MySQL.
Please do reply if you don't understand the requirement or have any doubt.
Looking forward for your valuable comments and suggestions
Note : Station names can be different for simplicity I have used alpha bates. Stations don't follow any alphabetic order.