I'm developing a reservation module for buses and I have trouble designing the right database structure for it.

Let's take following case:
Buses go from A to D with stopovers at B and C. A Passenger can reserve ticket for any route, ie. from A to B, C to D, A to D, etc.

So each route can have many "subroutes", and bigger contain smaller ones.

I want to design a table structure for routes and stops in a way that would help easily search for free seats. So if someone reserves seat from A to B, then seats from B to C or D would be still be available.

All ideas would be appreciated.

algorithm database-design
Edit
Report