Database Design

[vikrantmohite]

Hi Experts,

 

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

 

Regards,

Vikrant

 

Note : Station names can be different for simplicity I have used alphabates. Stations don't follow any alphabetic order.

[Illusion]

 

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.

 

 

Don't you think those two statements contradicting each other?

 

You need to some some how maintain the sequence .

 

Table Buses :  BusID, RouteID

 

Table BusStops: RouteID, PlaceID, SequenceID

 

Table Places:  PlaceID, PlaceName

 

You could assign sequnceid for a place... something like this

 

RouteID PlaceID SequnceID

12 34 10

12 23 20

12 45 30

12 12 40

 

 

suppose , in future if you want to add any station between places 34 and 23 - you could insert record with sequenceid  is that > 10 and <20.