Database Design

Databases
1

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 alpha bates. Stations don’t follow any alphabetic order.

2

create a table called something like bus_stops

primary key will be bus number plus stop number, data will be stop name

thus…

B123 1 A
B123 2 C
B123 3 Z
B123 4 N
B123 5 H
B123 6 K
B123 7 X

then to find a route that goes from C to H, do this –

SELECT stop_from.bus_no
     , stop_from.stop_no AS stop_from_no
     , stop_to.stop_no AS stop_to_no
  FROM bus_stops AS stop_from
INNER
  JOIN bus_stops AS stop_to
    ON stop_from.bus_no = stop_to.bus_no
   AND stop_from.stop_no < stop_to.stop_no
 WHERE stop_from.stop_name = 'C'
   AND stop_to.stop_name = 'H'
3

can you let me know the fields in table

I assume id(auto increment), bus_no, stop_no and stop_name are the only fields you are talking.

When I run the query I am getting “Column ‘bus_no’ in field list is ambiguous”

4

yes, except ~not~ including the auto_increment id

primary key will be bus number plus stop number, data will be stop name

the ambiguous bus_no in the query was probably due to my initial reply, which i edited a few minutes afterwards to remove that error

try it again please

5

Query is working fine.
But I just want to know what logic you have used to find the result. I am not good with queries. The stops in database may not be in order. In future if I want to change the route(add or delete the stops) the new entry will come in the last row.

6

That’s why you have the stop number column. That number indicates the order of the stops.
So taking this data

B123 1 A
B123 2 C
B123 3 Z
B123 4 N
B123 5 H
B123 6 K
B123 7 X

if in the future you want to add a new stop between the current stops C and Z, you’ll have to update the stop numbers accordingly


B123 1 A
B123 2 C
B123 [B][COLOR="#FF0000"]4[/COLOR][/B] Z
B123 [B][COLOR="#FF0000"]5[/COLOR][/B] N
B123 [B][COLOR="#FF0000"]6[/COLOR][/B] H
B123 [B][COLOR="#FF0000"]7[/COLOR][/B] K
B123 [B][COLOR="#FF0000"]8[/COLOR][/B] X
[B][COLOR="#FF0000"]B123 3 P[/COLOR][/B]