[phppick]

Hi,

I am designing Webbased Online ticketing system. I need some help on the Database design. I am using PHP with MySql.

Let me tell you with one example here..

Suppose a Bus which Starts at Location "A" and ends at Location "Z". In between that bus maybe passing through
other locations such as "B" "C" and "D". So that Route is

A -> B-> C-> D ->Z ( this one we are calling it as Route)

"A" is the Origin
"Z" is the Destination

B, C, D are Mid Point1, Midpoint2 and Mid point3.

The Route definition should be dynamic, if i want i can add or delete Middle points.
and some other Route maybe having NO Middle POints and some routes maybe having 10 middle points like that.
It all depends on Travellers which are running those Buses.
I need a design for this.

for example in the Above Route customer can buy the ticket from
A -> B
A -> C
A -> D
A -> Z

or B -> C, B -> D, B ->Z
or C -> D, C -> Z
or D -> Z

I) How to create the structure like this, and how to assign Prices for these...

II) and Main this is Ticket Buying Flow, for this Mid points.

Route maybe different , lets say a Bus from "A" to "Z" may go
like this A -> B-> C-> D ->Z
or A -> B-> W-> X ->Z where Price also differes.


As of now, i did like this.

1.Created a Location Table, in which i am storing Location Name and Code
2.Created a Stations Table, in which i am storing Station Name and Code, This one is for
Location Stations, for example Location(City) "A" maybe having different Bus Stops.
This one we can say as Pickup Points

3.Terminal Table, which will have Terminal Number of the Stations

4. After that i am defining Route, (current system having only from Point to Point, like A-Z only , no middle points)
By selection Start and Destination and assigning Adult Price and Child Price.

5. Finally is Trip Schedule, in this
a. select Route which is already defined
b. Select date and time

So, with these steps Trip is Scheduled, and Customer can book the ticket.


Thanks in Advance

Jen