Do you like going to the movies? Have you ever considered what the database design behind their reservation system looks like? In this article we’ll prepare an example database model for a movie theater.
There are a few assumptions we have to bear in mind:
- contemporary multiplex movie theaters can have one or more auditoriums within a larger complex,
- each auditorium can have a different number of seats,
- seats are numerated with row number and seat position within a row,
- a movie can have multiple screenings at different times, or it can be screened simultaneously in a different auditorium,
- for each screening a seat can be reserved/sold only once,
- we want to track who entered each reservation/sale into the system.
Let’s look at one possible database design to solve this problem (the model was created with Vertabelo for MySQL database):
Short table structure descriptions are given below:
movietable contains data about movies which will be shown in the theater. The primary key is
id, which is auto_incremented like all primary keys in all other tables. The only mandatory data is
All fields have meanings according to their name. The column
duration_mincould be used to disable inserting a new screening or to show an alert message in case we want to enter a screening in an auditorium where the previous screening is still in progress:
previous screening start time + duration_min of it > this screening start time
auditoriumtable identifies all auditoriums in theater. All data is mandatory.
seats_nofield can be used to calculate percentage of availability of auditoriums for a selected screening/movie/auditorium/date range. This is an example of data redundancy because we could get the number of seats for each auditorium by counting them in the
seattable. In this example it might not improve performance significantly. I show it here as an idea that could help with designing more complex models. If we set up the database this way we must bear in mind that if we change one piece of data, we also have to change others. If we add or delete data from the
seattable we have to adjust values
screeningtable contains data of all screenings and all fields are mandatory. A screening must have a related movie, auditorium and start time. We can’t have two showings in same auditorium at the same time. We can define a unique key consisting of
screening_start. This setup is better than defining a unique key consisting of
screening_startbecause that would allow us to enter screenings of two different movies at the same time in the same auditorium.
Vertabelo SQL preview code for this table looks like this (notice Screening_ak_1):
-- Tables -- Table screening CREATE TABLE screening ( id int NOT NULL AUTO_INCREMENT, movie_id int NOT NULL , auditorium_id int NOT NULL , screening_start timestamp NOT NULL , UNIQUE INDEX Screening_ak_1 (movie_id,auditorium_id,screening_start), CONSTRAINT Screening_pk PRIMARY KEY (id) );
seattable contains a list of all seats we have in auditoriums with each seat assigned to strictly one auditorium. All fields are mandatory.
reservation_typetable is a dictionary of all reservation types (by phone, online, in person). All fields are mandatory.
employeetable lists all employees using the system. All fields are mandatory.
In complex systems there are usually more roles so we need to have a role dictionary and employee/user-role connection. In our example we have only one role: the same person inserts reservations and sells tickets.
seat_reservedtables are the main tables of our system. This is why I listed them last. All other tables can exist without reservation tables but without the reservation tables we would lose the reason for designing the whole database in the first place.
reservationtable stores data about a ticket reservation and/or sale. If we have a reservation, the attribute
reservedwould be set to True, the
reservation_type_idwould be set according to the origin of the reservation and the
employee_reserved_idwould contain the
id_employeevalue of the person who entered data (it would be empty if the reservation had been done online by the customer). In the same way, if tickets were sold, the
employee_paid_idwould be filled with the
id_employeevalue of the person who sold tickets, the attribute paid would be set to True. The active attribute identifies if a record is still valid. If tickets were sold this attribute would always be True and the reservation without sales would be active until 30 min before screening starts
seat_reservedtable enables us to make a reservation or one payment for multiple seats. After the employee checks a few free seats on the interface, one record would be added to this table for each of them. If we want to check which seats are free or taken we can check the values in this table joined to the
reservation.active = True.
It is worth mentioning:
employee_reserved_idis not mandatory because a reservation might not exist for a seat (a ticket for a seat is sold without a previous reservation) or is done online
reservation_type_idis a foreign key referencing the reservation type’s “id”. It is not mandatory because a reservation might not exist (in case we made a sale without a previous reservation)
reservation_contactis a text input field for storing data of a person who made a reservation, it is not mandatory because a reservation might not exist (in case we made a sale without a previous reservation)
employee_paid_idis related to a user who made a sale, it is not mandatory because a sale might not have happened (seat was reserved, reservation was canceled automatically, seat has not been sold)
paidis a flag that indicates that payment has happened and is mandatory (values can be Yes/True or No/False)
In the end, bear in mind that nobody likes to find somebody else in his seat: