We require to make a mysql database that takes care of the following:
A bus leaves from point A to point D through points B and C. The bus has a capacity of 80 seats in total.
We require a MYSQL database designed to either php or stored procedures created to manage the booking to ensure that no over booking can take place.
Examples to note:
1. If 80 passengers book from A to D direct, then no other passengers can be carried in the way and so the system should not allow for passengers book the middle sectors
2. However, if in 1 above, one of the passengers is booked to alight at point C then it means an available seat for sector C to D hence that should show availability for a seat.
3. Passengers can book for each sector alone. e.g A-B, C-D.
We are flexible in how you design the database structure and any modifications you may need to do AS LONG as you arrive at the logic above.
For queries PM me.
Something close to this below is also acceptable
Bus Reservation System
The bus reservation system would allow any user to make a reservation and, eventually pay for the ticket. As a result, a reservation record is created and the user can select a seat. There are different models of buses therefore a bus configuration interface has to be created. Based on a particular bus configuration, an itinerary will be selected. There can be a variety of itineraries; therefore an Itinerary interface would have to be created.
A combination of an itinerary and bus configuration for a particular date is called a trip. Tickets can be bought for one way or round trip.
The Bus configuration interface would allow to enter the fleet of buses available for a particular company. The following are characteristics of buses:
1. Name (i.e. Neoplan Spaceliner)
2. Number of seats.
3. Number of seats per row.
4. Has TV
5. Has Air Conditioning
6. Has Sleeper
7. Has Meal
8. Seat space category (normal, wide, spacey, big recline)
9. Status (Active, Inactive)
The Itinerary configuration would allow to enter all the possible itineraries available for this company.
1. Identification (Kansas City, Laredo)
2. Start Day (Monday, Tuesday)
3. length (72 hours)
4. Stops (1 through 99)
5. Itinerary Stops
a. Stop Id (1,2,…)
b. Stop City
c. Arrival Time
d. Departure Time
e. Trip Price
The Trip File has information about each portion of an itinerary.
4. Departure Date
5. Departure Time
6. Trip Length (Hours & Minutes)
7. Itinerary ID
8. Bus ID
The reservations File contains a reservation for each trip or combination of trips.
1. Record ID
2. Reservation Id
3. Trip ID
4. Client ID
6. Paid Amount
7. Paid Date
8. Receipt Number
9. Change Seat Approve
A client may make a reservation for a particular trip or several trips at the same time and can be one way or round trip. There will be several records in the reservation file with the same reservation ID. There has to be a way to make sure that the seat will be available for the whole duration of the trip. If the passenger needs to change seats during the trip, a warning has to be displayed for the passenger to approve it.
The reservation interface will first display the itineraries for a departure date and time, the departure city and arrival city. The system will display an itinerary or a portion of itinerary. (i.e. for the itinerary Oklahoma City to Laredo with stops in Dallas, Austin, San Antonio), the passenger only wants to travel from Austin to San Antonio.
The system will search the trip database to select all records that stop in San Antonio and Austin with the same itinerary ID and will list the times.
The passenger will select one and continue to the seat assignment interface. If there are seats available for the entire segment that the passenger requests, only those seats will be displayed. If the seat has to change in the middle of the trip, it has to be marked in the seat assignment interface.
The seat assignment interface has to show graphically the layout of the bus.
Ajax has to be used as much as possible for this interface and also for the selection of the departure and arrival city.
There will be an opening and closing of day procedures based on the sales. Any standard procedure suggested will be accepted.
Interface available in Spanish and English strongly desired.