I've been asked to design a theatre seat reservation and ticketing system for a school project.
Here's the scenario:
A touring theatre company needs to allow online users to book seats for performances at different venues.
The database needs to store the seating layout for each show they do at a particular venue.
For example, the company might be doing Peter Pan this year and using all seats at the London Palladium, but next year they might decide to do another show at the Palladium using a reduced seating arrangement.
Therefore, each "seat" has to be assigned to a venue AND a show.
Currently, I have a table called "seats" which contains a seatID (primarykey), VenueID, ShowID, Row, SeatNumber.
When a user wants to reserve a seat for a particular performance, we check whether the seat for that performance and venue already exists in the Performance_tickets table.
Is this the typical way of doing seat reservations?
An alternative I considered was creating all the seats automatically when a performance is created and then having a seat status (i.e. reserved/not reserved).