How to Design a Database Model for a Movie Theater Reservation System

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:

1. The movie table 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 title.

   

   All fields have meanings according to their name. The column duration_min could 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

2. The auditorium table identifies all auditoriums in theater. All data is mandatory.

   

   The seats_no field 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 seat table. 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 seat table we have to adjust values seats_no in the auditorium table.

3. The screening table 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 auditorium_id and screening_start. This setup is better than defining a unique key consisting of movie_id, auditorium_id, and screening_start because 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)
   );
   

4. The seat table contains a list of all seats we have in auditoriums with each seat assigned to strictly one auditorium. All fields are mandatory.

   

5. The reservation_type table is a dictionary of all reservation types (by phone, online, in person). All fields are mandatory.

   

6. The employee table 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.

7. The reservation and seat_reserved tables 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.

   

   The reservation table stores data about a ticket reservation and/or sale. If we have a reservation, the attribute reserved would be set to True, the reservation_type_id would be set according to the origin of the reservation and the employee_reserved_id would contain the id_employee value 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_id would be filled with the id_employee value 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

   

   The seat_reserved table 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 table where reservation.active = True.

It is worth mentioning:

• employee_reserved_id is 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_id is 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_contact is 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_id is 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)
• paid is 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: