Help with a toughie please :)

General Brainstorming Required please

The scenario is this:

I would like to create a webpage which allows a user to log onto a website, view a plan of a concert hall / seating arrangement and be able to click on a specific seat, book, and pay online for it.

When they have done this ideally i would like this seat that they chose, booked and paid for to change to a different colour (red = booked, green = available).

Does anyone have any suggestions on how this can be done??
any suggestions would be greatly appreciated

Thanks guys,

Floz from Aus.

id start by scanning the seating plan in opening it up in fireworks make a heap of roll overs over the seats give each seat a hotspot and a unique id.

hop into mysql create a table with a table that looked somthing like this
id
seat_id
booked (yes/no)
booked_id (person who booked it give them a unique id)

check seat id with seat id in db and see if it was booked if it is either diable link and change colour or just change seat colour

when user clicks on seat
if (seat == booked)
{
redirect back to seating plan
}
else
{
give user a unique id from the current session and store it in a session var
show user a form to enter credit card
check form for any mistakes
if none
take user id and form details and insert into db
}


just an idea thats where i would start you could even do it through flash

for paying online, you need to choose a method. or better: several.
you can register with companies that do credit card billing, paypal and similar services. they´ll supply the info how to connect to them. (basically you redirect the user to a ssl-secured site of theirs and pass the amount to be billed and an url to return to after the customer finished. they´ll care about everything else. but they want their share...

for the software side: i would make a database.
Tables:
1. hall (id int 11 unsigned auto_increment, name varchar 255, address text, primary key id)
2. bookings (id int 11 unsigned auto_increment, hall int 11 unsigned, seat varchar 255, starttime int 11 unsigned, endtime int 11 unsigned, primary key id)

Explanation:
First, i always use "id ..." as primary index, it´s kinda habit.
The table "hall" will have a list of all concert halls (of which the user has to choose one first).
The table "bookings" will contain all booked seats. "hall" refers to the id in table "hall", "seat" is the seat number (non numeric so you can have 1a, 1b, VIP 1, ...) and starttime and stoptime are the unix-times (also a personal habit) of the booking start and end.

What you could do additionally:
- make a table "plans" where you put the seat plans (in a machine-readable way so you can check for false input and build the graphic seat plan from a script)

What else you need to care about:
One scenario that i can imagine where you need to take special measures is this:
- Client A wants seat 1 from hall 1. He wants to book, the seat is free and he is forwarded to the credit card billing page.
- in this moment, Client B also wants the same seat. He does the same.
- now, when Client A returns and you get a positive billing answer from the credit card company, you book the seat for Client A.
- You can´t stop Client B from paying for the same seat now anymore since he is not on your pages anymore

... just a little brainstorming ...
M.

I don't see how you're going to stop the race condition that M. Hirsch sees. However, some potential solutions:

- Don't guarantee seating. Let people reserve it, but don't let them know if they got it until you pull down for batch. Let them choose a series of seats. I.e. "if I don't get a, give me b, otherwise I'll settle for c". Email them to know which seat they got, if any. No seat = no charge. This would work best if you could get connected to a payment service that would let you hold all of your payment requests until batch time.

- When a user selects a seat to be booked, it's reserved for 5 minutes or whatever. Keep a record of who asked for what seat. After payment is received for a specific seat or seats, redirect to a page that actually finalizes the transaction. That way, if User A, B, and C all book seat 1 row 1 in this order:

A
B
C

Then B will be told when they reserve "sorry, this seat is temporarily reserved, please wait a moment to see if it becomes available". C will get the same. If A fails to pay, then B sees "The seat has become available!" and has x minutes to pay. If B then pays, C gets "Sorry, this seat has been taken for this concert. Please select another seat". It's still race, but you get a little more control. Caveat: It could really annoy users.

Keep thinking... there have to be better solutions than these two.

Hmm some good thinking there ... thankyou all

That is a point ... i could do it through flash ..

The credit card payment part is taken care of. Going through a bank which provides this service. We have to provide them with a unique invoice number/reference # for each transaction, and the amount, and when the user clicks "pay now" they take over. Saves me the worry :P
The 2 people in 1 seat scenario is a little worrying, yes. A catfight on the night would not be good.

Unfortunately i dont think the bank would let us "hold" payments until batch time ...


Thanks for your thoughts and keep them coming in. Surely there are other ways

Floz.