Seat Reservation Structure

[CaptainNick13]

Hi guys, it's been a long time since I've had to build a database and am struggling with one section for a seat reservation system.

I currently have 4 simple tables as follows:


Customer-
+CustomerID
-Surname
-Forename
-Telephone

Play-
+PlayID
-Title
-Author
-LeadActor

Playbill-
+PlaybillID
+PlayID
-Date
-Time

Reservation-
+ReservationID
+CustomerID
+PlaybillID

The end result of the database is for the user to book 1 or more tickets for a viewing of a play on a given date & time. My current problem is creating some kind of instance of a floor plan to contain the seats so I can seat a seat (example D9) as reserved in a showing at 4pm without it becoming reserved for all showings on all dates etc.

My initial idea was a Floorplan table and a seat table but when I come to design these parts they look wrong and will obviously not work how I want them to.

Does anyone have any suggestions as to how i can make this work and what further tables I would require?

 

[Steve R.]

Take a look at Database Answers. See item #104 which has a variety of reservation systems. Please note that I have NOT used this website, so I cannot say whether it is good or bad. But it is a start.

As an aside, you really need to have two customer ID types. One used to establish relationships. The other would be used to actually identify the customer. This concept would also apply to other unique ID types.

 

[CaptainNick13]

Thanks Steve, the closest model I can see is the cinema bookings model, however this is overly complex for what I am attempting to do i think. There is only one theater with only one stage so only one production at a time. My system would just focus on reserving tickets for future productions. I just don't know how to build the rest of my tables to stop reserving one seat for all productions instead of just the one requested

 

[Steve R.]

Just as a quickie concept, think of each seat/time slot as an array. Basically something like A123PRODA where A123 would be a seat number and PRODA would be the event number. Then you could have four "states": open, reserved but not paid, reserved and paid, ticket picked-up.

 

[CaptainNick13]

A very interesting idea but how would it be implemented? Seems as though there would be no real automated way to creating all the seating when a new event is entered onto the system.

 

[Steve R.]

As I drove home from work, it occurred to me that there is a better solution. Essentially a table with at least four fields.

SeatNumber, ProductionNumber, TicketState, and CustomerID. SQL would then be used to retrieve specific information for a particular reservations. So if you want to see all seats that are available for a particular production you would retrieve all seat numbers where the ticket state is equal to 0 AND the production number = X.

It will be up to you to put this all together.

 

[CaptainNick13]

I'm pretty much a beginner when it comes to this kind of thing but I'll see what I can throw together, thanks for the assistance though, it's very much appreciated!

 

[ChipperT]

I would think that the airline reservation system in the library would be fairly close to what you are looking for and could be readily adapted to your needs. After all, flights have seats that must be reserved, paid for and each flight is like a new play - you start fresh.

 

[CaptainNick13]

Which library would this be? On this forum?

 

[ChipperT]

Which library would this be? On this forum?

http://www.databaseanswers.org/data_models/index.htm

 

[Steve R.]

Seems as though there would be no real automated way to creating all the seating when a new event is entered onto the system.

You will need a table that is simply a list of all available seat numbers. That allows you to reuse the seat numbers for every event.