tips on my booking system please

[Elliott]

Me again 🙂

I am working through an assignment for uni, and have come to a brick wall.
It is regarding a booking system for a fictional theme park. I am not asking for a complete solution to this, but just a programmers way of thinking about it, as im not as experienced as id like to be with php.

Here is the area of work i am stuck with:
The Excersise is to create a simple database for a theme park allowing rides to be booked using HTML forms for a single day. Confirmation of the booking should be provided in the form of a webpage showing the name of the individual making the booking and detailing the rides and seat numbers booked, The confirmation of the booking should include the cost of each ride and the total cost for booking including VAT at the standard rate.

your Database should hold sufficient spaces for all the seats on all the rides for at least 3 hours on a single day, this will enable you to show all the functionality.

Ride info...
Swinging Ship - 5 rows of 4 seats - 30 mins duration - £1
Roller Coaster - 4 rows of 4 seats - 20 mins duration - £3
Big Wheel - 8 cars of 4 seats - 30 mins duration - £1.50

Log Flume - 10 boats of 4 people - 20 min duration, but boats leave at 2 min intervals. £2

System must prevent double booking, and over booking of rides.

The booking algorithm and form must allow several seats to be booked for the same ride and there should be a checkbox on the booking form in which the customer can indicate whether or not all the seats must be adjacent( on the same row or in same car)

Be able to place a single booking through whuich multiple seats are booked either for the same ride or for different rides or even several seats booked for several rides.

Keep track of booking ID accross the pages.

Confirmation email sent out showing rides, time, seat numbers etc.

And the mental bit at the end:

Make it so there is an option to plan a days riding, so that the group or person can book so that they can go from one ride to another with not less than 10 mins between rides.

I have created various tables such as booking, customer, rollercoaster, bigwheel, swingingship, logflume, etc

I have created a format in the ride tables to represent the layout of the ride to make the booking easier(i hope).

Time Rownum seatA seatB seatC seatD
0900 1

0900 2
0900 3
0900 4
0930 1
0930 2
0930 3
0930 4
1000 1
etc etc etc.........

I would really appreciate any tips on how to start this booking bit. I am totally stumped and have been for a few days now.

Thanks a lot in advance

Elliott

[vornn]

The smell of a challenge during the evening, nice.

I like these assignments.

OK, Elliot, let's take a look.

A booking system for a theme park.
Each ride having a number of seats.
Each ride having a set time limit.

I would look at two tables to start with:

tbl_rides - with ride name, number of seats in a car, time allowed for complete run, number of cars, cost, etc.

tbl_booking - with time, date, ride, some form of id.

The booking would allow the user to select the ride they wanted, and the approx time for the ride, the number of patrons and if they wanted to be in the same car.

The customer ref is stored in a session var and used in booking all the rides.

The back end script would check the following:

The number of places booked
The time booked
The number of places available in the cars for that time.

The script would return:

A time for the next car with sufficent space for the booked party
The ID number of the booking

THis is nice, got me thinking now. I'll give you some more help if you want it.

Let me know if this helps...

🙂

[Elliott]

Thanks a lot for the reply.

Its pointed me off in some sort of direction 🙂

So, i would want to scrap those tables i mentioned in my post, and try with some simpler tables as you sggested.

Some things im still unsure about are:

How do i check the rows in the table to see if there is a booking?

Would i need a field for each seat, or just a seat total etc....
....if so, how would i be able to return a seat number to the user?

would the $_SESSION['customer_ref'] be in the included ('connect.php') or somewhere on each page.

and when booking a ride this way, how would it allow mutliple rides to be booked in the same transaction?

Thanks again.

Elliott

[kiss_the_robot]

The assignment sounds like a cross between an airline flight booking system, and a shopping cart.

Perhaps it would be useful to create one table (say we name it "ride_list") with the name of each ride, and a series of "flight numbers" (the label for each ride interval). This table would need to include two timestamp fields, one for the ride start time, and one for ride end time.

Then you could create another table which holds the specific seating arrangements/occupancies in "aisles" and "rows" associated with each "flight number", ordered by ride name. Call it "flight_list".

The third table could be a temporary table that stores and tracks the booked "flights" of the customer according to their session id number. (The same principle as a shopping cart).

I'm assuming you are using a mySQL db for the application, so a join would be required to retrieve the customer's "cart" (or in this case, schedule/itinerary):

$query = "SELECT * FROM ride_list as r, flight_list as f, tempCart as t WHERE r.ride_id = f.flight_num AND t.sess_id = '$sessid'";

Every time a customer adds a ride to their itinerary you could get the value from the "end_ride_time" timestamp field of the flight they previously added, write a function to append 10 minutes to the end time, and the result would be the "start_ride_time" value for the next booking. You could pass this value in the url that increments upward, so the url would look something like:

bookrides.net/itinerary.php?newtime=10:45

This way, you could make a query to the db that displays all available flights that start only after your "newtime".

Uh... hope this helps...

[vornn]

First things first...

So, i would want to scrap those tables i mentioned in my post, and try with some simpler tables as you sggested.

I'm trying to get you thinking about how to approach the database design, if we asked all the user on this board, you would get as many answers. Thinking about the design, I thought that the simplist way was to have two tables, maybe three (if you wanted to store customer details).

This design would minimise the amount of data and time in the processing of the record returned.

How do i check the rows in the table to see if there is a booking?

Right, I had one a while ago, I was asked to create a speeddating website, with full automation in the backend. The matching script checked the person against the other sexes ticks. SO male a ticked female 1, 2 and 3, but there was only a match if female 1 also ticked male 1.

Back to the Theme park.

Search the records to show a list of the availabiliy of the selected ride, like:

select from bookings where ride = 'ridename'

we then need to further filter the search with an and, like:

select from bookings where ride = 'ridename' and time = 'selectedtime'

What happens if this time is full, how many records are there for the ride and time? We could store the details for the number of places and match this with the number of records for the time slot for this ride.

Does that make sense?

Would i need a field for each seat, or just a seat total etc....
....if so, how would i be able to return a seat number to the user?

Each booking would be allocated a number, dependent on the order of booking, so the first one would be seat 1, then 2, 3, etc.
These would be in the bookings row. The next question would be if I was to book 4 seats on a ride, do I create 1 record or 4?

would the $_SESSION['customer_ref'] be in the included ('connect.php') or somewhere on each page.

I would add that to the top of each page.

and when booking a ride this way, how would it allow mutliple rides to be booked in the same transaction?

This really calls for some kind of shopping basket. Now can you use existing code, or do you have to create the whole site yourself?

If you can include librarys, then something like wfcart could be used to store the information.

Hope this helps...

🙂

[Elliott]

ah i think im beginning to understand you (finally)

the booking table would start off empty. Unlike the way i planned to have a structured table with slots for each seat.

The booking table would be empty until a booking was made, then it would contain a time_selected, the ride selected, customer ID, and somehow...the seats/rows.

(im gradually understanding 🙂 ) - i think.

now then, i kinda think this is a good idea, and is a lot simpler than the way i was planning on doing it. But i still cannot grasp how to do something like:

If a person wanted to book 3 seats on the log flume at 10am.
this would make a row with a booking_id, 10:00 in the time field, and log_flume in the ride, with the customer id in the customer field.

So this would book 3 of the seats from the 10am ride of the log flume.

How would this know which 3 seats were booked? and what happens if the person after books some seats? will it know that there is still one seat on the previous row available, then book the rest on a new row?

Thats the hardest bit im finding to understand.

Sorry for all the dumb questions.

[vornn]

OK, so booking three seats...

Just ask the customer how many seat they want.

then run a loop to add three record for that customer, or build something in for three tickets and check on this at run time.

Sounds good.

You getting this?

If you know how many seats are required, then check the next car, how many places available, enought? if yes add to this car, if not, add all to next car.

Easy?

[Elliott]

I understand now what u mean.

But this leaves the problem of finding out a specific seat number on the ride they are booked on....eg seat 14 or A2 however it will be represented.

If i do it simply by checking there are enough free seats, and deducting the amount booked from the amount of free seats, i cannot get which particular seats they are booked on.

Also stated in the brief, the user simply puts in a ride, and a number of people, and a time, then it would fill the gaps of the various rows which have not been booked (using test data). Or put the people together in one row, if they so choose.

Here is the assignment brief if u wanna read the whole task.

http://www.aptfinance.co.uk/ecommerce/ass.htm

I bet you are really wishing you hadnt replied now hehe 🙂

Elliott

[vornn]

Hell no, I'm loving every minute of this.

😃 😃

I would put them all together by default, then you know there won't be a problem with splitting.

I'm off to play CS:S now, I'll check leter on and see if you need a little more help.

🙂

[vornn]

I don't know about you, but I'm going for the 70% + mark.

😃 😃 😃 😃 😃 😃

[Elliott]

yea, done most of the criteria already for the higher marks. but not managed any of the booking bit yet heh.

Ive changed my booking forms, to now have a couple of steps.....
step 1 gets customer details, surname, firstname, email address, and assigns an autonumber for the customer_id.

Then next goes onto the ride booking page, which will add the customer details to the customer table, then display the form for ride and time selection, and number of seats.

Then while flying through the forms i crash straight into a brick wall again 😕

Now when the first form is submit, the customer details go into a customer table as i said, which assigns an id.

Now i can use this ID in the booking table.

On the ride booking form all i want to have is....
which ride they want to book..
at what time they want to book it...
how many seats they want....
and if they all want to sit together.

Now as you said, i could put them all together as the default selection, but what happens if there are 2 seats in row 1, and 1 seat in row 3, and 3 people are not bothered where they sit. how do i get them into those gaps?

And on the confirmation page, how do i tell them what seat numbers they are in?

These are my main obstacles at the moment. Once i understand how my bookings will be held in the table i can crack on with setting up my table and testing it.

Cheers,
Elliott

[Elliott]

It finally clicked this morning what u were meaining here:

Each booking would be allocated a number, dependent on the order of booking, so the first one would be seat 1, then 2, 3, etc.

So...i start with an empty table. Fields are time, seat, ride, booking_id(P), customer_id.

Now, i think this would be really simple to check if its full or not, or how many spaces are available. Comparing it to the max capacity, to the num_rows etc.

But when it comes to booking seats, how will it do it?

I know that the seat would be the number of the seat, and this can somehow be auto_assigned, but would need to stop at 20 or max capacity.

Heres an example of the customer booking:

baring in mind it has 5 rows of 4 seats.
1234
5678
etc....

Fills in form
wants 3 seats for rideA at 0900.
INSERTS into table, assigning seats 1, 2, 3.

Next person comes
wants 2 adjacent seats on rideA at 0900.
INSERTS into table assigning seats 4,5
this makes them on different rows in the ride.

So table would look like:

Booking_id time seat ride customer_id
1 0900 1 A 1
1 0900 2 A 1
1 0900 3 A 1
2 0900 4 A 2
2 0900 5 A 2

etc...

How would this work if i used this method?

Without a structured table like in my first post?

Cheers
Elliott

[vornn]

Bingo, I think you've got it.

Now then, let's see about the function.

You don't need to auto assign the seat numbers, just assign them incrementally.

for example:

patron A books 4 seats - he gets the first four in the car (front row, hot property!)

patron B books three seats - he gets seats 5, 6 and 7.

If you know how many seats have been booked, then you know what the next number is and how many are left. It's all math from now on.

Your booking id should be different for every row in the database. Set this to auto increment in your database, then make up a key from the date and the customer reference number. This takes into account returning customers at a later date.

Hope this helps...

🙂

[Elliott]

ah brilliant, now i can make a solid start on coding.

one more little question:

regarding booking adjacent seats, how would this method be able to book 3 seats on first row. then next person wants 3 seats next to each other. How would it miss out the 4th seat on the 1st row and go onto second row of the car?

I did think of some kind of weird formula to work out which seats are on which row, dont know if it will be any use tho.

row1=($seats_per_row 1)-3 to ($seats_per_row 1)
row2=($seats_per_row 2)-3 to ($seats_per_row 2)
going off there being 4 seats in a row, which there are.

was thinking it could check 1 to 4 to see if it could fit them on, then couold check 5-8 etc etc.

also......
would it be better to save all the variables, in the session variables and write to the database all in one go, when all rides have been booked? or write after every ride booking?

Thanks once again,
Elliott

[vornn]

I was wondering when you would ask this.

How do you book someone into a new row, and keep the forst row open for single people?

All it needs is one field in your ride data, telling you how many seats on each row.

Then you have a little math, and the answer is there.

😃

But what about the last place? How would you sort that?

Well you know how many seats have been booked, then it's just a simple check to see which seats are available.

Now then, let's restart the project using Object. (only kidding).

😃