A Data Model for Online Concert Ticket Sales

What kind of data model makes online concert ticketing work?

Have you ever been to a concert? I'm sure you have. And I bet you purchased your ticket online. Online shops let us buy tickets to concerts by our favorite artists without leaving our comfortable homes. We can find a venue or a location (even one that’s not near us), choose a seat, and get our tickets delivered instantly through email – or maybe we’ll wait several days and get the tickets delivered. Either way, it’s a lot easier than standing in line!

If you've ever wondered how the online ticketing process works, now’s the time to find out! Let's dive into the model and see for ourselves!

The Data Model

In this example, we’ll describe a model for an online ticketing site that sells only concert and music festival tickets. Usually, these sites sell tickets for lots of different things, such as plays, sporting events, comedy shows, etc. Since our goal is to show how the data model works, we’ll focus on the music segment. This will allow us to explain the model in more detail. Besides, offering tickets for other events is not significantly different. The principles are basically the same.

The data model consists of twelve tables divided into three subject areas. Those areas are:

• Concerts
• Tickets
• Customers and orders

We’ll go through each subject area in the order it’s listed.

Subject Area 1: Concerts

This subject area manages information about the concerts to which we’re selling tickets. It contains seven tables:

• genre
• artist
• role
• concert_role
• concert
• venue
• concert_group

First up is the genre table. It stores a list of all the musical genres of our concerts. This allows customers to search for tickets in their preferred genre. The table consists of the following attributes:

• id – A unique ID for each genre.
• genre_name – Defines each genre (e.g. “rock”, “jazz”, “classical”, etc.). This is the alternate key of the table.

The artist table is a list of musical artists. It’s connected to the concert_role and concert tables. It has the following attributes:

• id – A unique ID for each artist.
• artist_name – The name of the artist (e.g. “Paul Simon”, “The Rolling Stones”, “John Scofield”, etc.).
• genre_id – References the genre table so every artist can be categorized according to genre. We’ll assume that every artist has only one genre.

The next table is the role table. It contains a list of roles that an artist can have in concerts that are not their own. For instance, a singer could be an opening act or a special guest in rock band’s concert. This table has the following attributes:

• id – A unique ID for each role.
• role_name – The name of the role. This is the alternate key for this table.

Next, we have the concert_role table. It is an intersection table that will be used to allocate a role to every particular artist for every particular concert (other than their own). It contains three attributes:

• concert_id – References the concert table.
• artist_id – References the artist table.
• role_id – References the role table. The artist_id – role_id pair is the alternate key of this table.

Those three attributes form a composite primary key, which means that any given combination of all three attributes can occur only once in this table.

The next table in our model is the concert table, which stores details about all our concerts. It contains the following attributes:

• id – A unique ID for each concert.
• concert_name – The name of the concert (e.g. “Homeward Bound – The Farewell Tour”, “Glastonbury Festival”, etc.).
• artist_id – The unique ID of the main artist.
• date – The date and time when the concert is being held. The concert_name – date pair is the alternate key of this table.
• venue_id – References the venue table.
• concert_group_id – References the concert_group table; this can be NULL because there can be concerts with no special ticket category.

Now we have the venue table. This table will contain information about the venue where the concert is being held. It consists of the following attributes:

• id – A unique ID for each venue.
• venue_name – The name of the venue (e.g. “Ziggo Dome”, “Royal Albert Hall”, “Wembley Stadium”, etc.).
• location – The location of the venue.
• type – The venue type (e.g. “concert hall”, “theater”, “stadium”, etc.).
• capacity – The capacity of the venue.

The last table in this subject area is the concert_group table. This table will be used to allocate a group to a particular concert. This is because concerts can be a) individual, b) included in a music festival daily ticket, or c) part of a festival but requiring a separate purchase. This table will have the following attributes:

• id – The unique ID of each concert group.
• name – The name of the concert group.

Subject Area 2: Tickets

This area stores information about tickets and makes the connection between available concerts and customer orders. It consists of two tables:

• ticket
• ticket_category

Tickets are our only offering. So, it is important that we don't mess this up and sell tickets for non-existing seats or sell the same seat to several customers. To avoid that, we'll first have to create the ticket table. This table will contain the following attributes:

• id – A unique ID for each ticket.
• serial_number –The ticket’s serial number. This is the alternate key for this table.
• concert_id – References the concert table.
• ticket_category_id – References the ticket_category table.
• seat – The seat for which that ticket was bought; this can be NULL because tickets can be for the standing area or seats can be unmarked.
• purchase_date – The date when the ticket was purchased.

The second table in this subject area is ticket_category. It contains the following attributes:

• id – A unique ID for each ticket category.
• description – A description of the ticket category, which depends on the date of purchase, area, and/or seat.
• price – That ticket category’s price.
• start_date – The earliest date when a ticket of this category can be purchased; this can be NULL because the ticket category does not necessarily depend on the purchase date.
• end_date – The latest date when a ticket of this category can be purchased; this can be NULL because the ticket category does not necessarily depend on the purchase date.
• area – The location (within the venue) that determines the ticket category (i.e. front-row seats are more expensive than those further back); this can be NULL because the ticket category does not necessarily depend on the area.

Now we’ll move to the last subject area!

Subject Area 3: Customers and Orders

As it does with most online shops, this area keeps track of our customers and their orders. This, of course, includes current and historical data. Here we have three tables:

• customer
• customer_order
• order_ticket

The first table contains all the relevant information about our customers, so we will call it the customer table. We will need the following attributes:

• id – A unique ID for each customer.
• customer_name – The customer’s full name.
• email – The customer’s email address. This is an alternate key for this table.
• user_name – The username chosen by the customer during account creation. This is another alternate key for this table.
• password – The hash value of the password that was chosen by the customer.
• confirmation_code – A code sent to the customer during the registration process. It is used to confirm their account.
• confirmation_time – When the account was confirmed by the customer.

The customer_order table contains all the data about the orders placed by our customers, namely:

• id – The unique ID of that order.
• customer_id – References the customer who placed the order.
• order_time – When the order was placed.
• delivery_address – The address where the customer wants their ticket(s) delivered; this can be NULL because most of the time the customers only want an electronic ticket.
• delivery_email_address – The email address where the customer wants to receive their electronic ticket(s).
• preferred_delivery_time – When the customer wants their order delivered; this is NULLable because most customers want an electronic ticket.
• time_paid – The timestamp of when the customer paid for this order; this can be NULL because the customer may not pay at the same time the order was placed.
• time_sent – The timestamp of when the order was shipped; this can be NULL because the customer can choose electronic tickets.
• total_price – The total price of the tickets ordered.
• discount – The discount given to the customer (if any).
• final_price – The total price of the order after the discount is applied.

We've come to the latest table in our data model, which is the order_ticket table. This table has the following attributes:

• id – The unique ID of the ticket being ordered.
• customer_order_id – References the customer_order table.
• ticket_id – References the ticket table.

The customer_order_id – ticket_id pair is the alternate key of this table.

Now we can happily announce that, by creating this table, we've also managed to create a data model for online concert ticketing!

Did You Find This Online Ticketing Model Interesting?

I had lots of fun creating the online ticketing data model. Maybe it's because I attend quite a lot of concerts – and am a regular customer on several online concert ticketing sites! But I also think that this model can be used as a basis for other data models. I've tried to show the general principles in this example. Have I succeeded? What do you think about the model? Is it too simple? Too complicated? Did you find it helpful in your own work? Please let me know in the comments section.