How to Plan and Run Events:

An Event Management Data Model

Emil Drkušić, Database designer and developer, financial analyst

by
Emil Drkušić
Database designer and developer, financial analyst

Posted: February 22, 2017

An Event Management Data Model

Organizing an event is a lot of work! In this article, we examine the data model behind an event organization app.

If you’ve ever tried to organize an event for more than ten people (and don’t count parties or business meetings here) you know how complicated event management can be! Have we invited everyone? Have they confirmed if they are coming? Is the venue booked and prepared? Who will host the event? Who will participate in the various parts? There are many other questions to answer, and things could easily go wrong.

You can do all your planning with paper and pen, but why not use an app? It’s more convenient! Any app will need a place to store all the necessary event information. This is where our event management data model enters this story. Grab a coffee, settle into your favorite chair, and we’ll look at what it takes to build an event management data model.

Event Management FAQs

Before we explain the model and describe how we’ll store the data, let’s first review some event management basics:

  1. What could be considered an event?

    In this context, an event is an occasion where many people, who often don’t know each other, gather to learn about or participate in something. Some popular events are music festivals or concerts, IT conferences, sporting events like football games, health and medical conferences, etc.

  2. What do all events have in common?

    The previously-mentioned event examples are very different in terms of content, purpose, and target audience. Still, they share many similarities, especially in their organization.

    First, consider the event’s content. Some events (e.g. a concert or a football game) will provide only one type of content and will be held in one place. Other events include many different but related “sub-events”, which may occur in various places.

    Take an IT conference as an example of the second type of event. There are lectures, presentations, workshops, and competitions. Attendees will probably go from room to room or may even travel between different buildings as they go to various sub-events. Some of these sub-events will run at the same time, but each sub-event still relates to IT and has one or more hosts.

  3. What does it take to make an event successful?

    First of all, there are many event venue personnel who work hard in the background: audio and visual techs, ticket sellers, ushers, cleaning and maintenance workers, and administrative personnel. Many people in many different roles will spend many hours working hard to get the stage ready for the “stars” and other participants, but none of them will get much recognition.

    Clearly, all events require some kind of infrastructure. If we hold a conference in a physical location, we’ll be talking about rooms and seats, a sound system, lighting, maybe video, etc. Even an online event, like a webinar, must have a place to produce the content and the IT setup needed to connect with virtual attendees.

    Events usually have media sponsors and partners that help in organizing and promoting them. These sponsors are mostly companies and associations related to the event topic; occasionally they are other companies looking for some good publicity; and more rarely a private individual will serve as a sponsor or partner.

  4. What is event management?

    Event management is a process used to effectively manage events and everything related to them. It could be considered as a type of project management. We discussed a project management data model in this article. Using a Gantt chart to show the event’s progress, current status, and future actions is not a bad idea.

    We’ll probably want our event management application to fit on one screen, if possible. Most actions – like creating a new show, assigning employees and resources to a task, or estimating costs – should be drag and drop.

The Data Model




The data model consists of three main subject areas:

  • Events and Partners
  • Shows, Performers and Equipment
  • Employees

We’ll take a closer look at each subject area in the order they are listed.

Section 1: Events and Partners

Events and Partners subject area

The Events and Partners subject area is the central part of our model. In these five tables, we’ll store the most important details about our events. We’ll also relate events with partners.

Let’s start with the event table. This lists every event we’ve organized and every event we plan to organize. The attributes in this table are:

  • event_name – The name of an event. It’s not UNIQUE because we may have two or more events with the same name – e.g. a concert by the same band would have the same event name. However, the event_namestart_time pair should be UNIQUE.
  • event_type_id – References the event_type dictionary.
  • event_location – Describes the location where the event will take place. Using a descriptive attribute lets us avoid building a more complex model with tables like “country” and “city” and attributes like “address” and “description”.
  • event_description – A detailed description of the event and all shows or activities associated with it. For a concert, this is where we would store info on the opening act, the main act, any additional entertainers, and the performance order.
  • start_time – When the event will start. It’s mandatory because we should know this in the planning phase.
  • end_time – When the event ends. We could use this attribute to store the expected or actual event end time. Since we may not know this exact time in advance (e.g. if a sports game goes into overtime), this attribute is optional.

The event_type dictionary classifies the events we handle. We’ll store all possible types of events according to their niche: concert, football match, basketball game, IT conference, etc. Each event type is uniquely defined by its type_name.

As we previously mentioned, events usually have partners. Most events will have at least a media partner, while some will also have sponsors and other partners. The same partner could have several different “partner roles” on the same event. For example, a television broadcast company could be the media partner and the general sponsor of the event at the same time. This is why we’ll use three tables to relate events with partners.

It is important to be able to add partners in the planning phase so that all event stakeholders can have timely access to that info. Also, we may use past data when we’re planning new events – e.g. we could contact the same partner when we’re organizing a recurring event or a new event of the same type. If a company was general sponsor of a tech conference last year, they may be interested in doing it again this year.

Now, let’s look at the three partnership tables. The first is the partner catalog. For each partner, we’ll store the partner_name and their address, contact info and other partner_details. Notice that the partner_name attribute is not unique. We may have two partners with the same name, such as two private individuals with the same first and last name or two companies with the same company name. In this case, we’ll distinguish between them using the info stored in the partner_details attribute.

The second table is the partner_role dictionary, which lists all the different roles a partner could have. The role_name attribute will contain only UNIQUE values. Some expected role names are “media partner”, “general sponsor” and “sponsor”.

The last table in this subject area relates partners with events. The is_partner table contains only foreign keys that relate partners with events and define roles or partnership types. The combination of these foreign keys forms the UNIQUE key of the table. If we wanted to, we could add a start date and an end date in case some partner only fills their role for part of the event. We could also relate partners with single sub-events and rather than entire events. Still, these are relatively uncommon situations, so we’ll leave this part of the model as-is.

Section 2: Shows, Performers, and Equipment

Shows, Performers, and Equipment subject area

As mentioned in the introduction, each event can have several sub-events. In this model, I’ve decided to call the sub-events “shows”. A show is a single sub-event, focused on one topic, having at least one performer, etc. In an IT conference event, one show could be a lecture on project management principles; another show could be a panel discussion of data warehousing best practices. Both could take place at the same time, in different locations, and be hosted by different presenters. We’ll also define everything that is needed to run a show, because the show must go on (in any case ☺ ).

The central table of this section is the show table. This will keep a record of any show associated with past, present, and future events. When we’re planning an event, we’ll need to add new shows as soon as the performer (i.e. lecturer, speaker, presenter, rock star) has agreed to be part of an event. Looking at a description of the table’s attributes will help us understand how it works:

  • show_name – The name of the show.
  • show_location – Describes where the show will take place.
  • show_description – A detailed description of that show.
  • start_time – The expected start time.
  • end_time – The expected end time. It can be NULL because we may enter the actual end time (once the show is over) rather than the expected end time.
  • event_id – What event the show is part of.

In most cases, shows will require equipment and performers. (Theoretically we could have a show without a performer, but we won’t bother with that here.) Because equipment is limited, It’s important to reserve all that’s needed in the event’s planning phase. To do this properly, we need to know what is going to happen at what time. For example, if we have two projectors and two shows requiring projectors scheduled for the same time, we can’t add a third projector-requiring show for that time unless we get more equipment. This is the kind of information we must have in the planning phase.

Moving on, we have the performer table. This is a simple catalog of every performer we’ve worked with or will work with on any event. For each performer, we’ll store their full_name. It could be the name of a band, a lecturer, etc. The genre attribute is here to distinguish among the various types of performers – e.g. rock bands from sculptors. The last attribute in this table stores performers’ contact_details. We’ll use the text data type to store the lot, but we could also split contact details into a few separate fields.

We’ll relate shows and performers via the participate table. The attributes in this table are:

  • show_id and performer_id – References to the related show and the performer. This pair could be an alternate (unique) key of the table but I decided not to use it; we might have one performer be part of the same show at two different times.
  • start_time and end_time – Exact times that define when that performer was part of that show.
  • cost_planned and cost_actual – The costs/fees we expect to pay a performer and what we actually paid them.

The remaining three tables are used to define all the equipment needed for a show.

The equipment_type dictionary categorizes equipment. For a concert, these categories could be “lighting equipment”, “musical instruments”, “stage construction”, etc. The type_name attribute contains only UNIQUE values.

The equipment table describes equipment items and quantities. Its name attribute defines the equipment more specifically than equipment_type.type_name. For a disco ball, its “equipment”.”name” value would be “disco ball” but its “equipment_type”.”type_name” would be “lighting equipment”. The available attribute defines what quantity of the item is available to us. It’s a decimal number because maybe we’ll use some “items” that can’t be enumerated, like water and electricity.

The last table in this section relates equipment and shows. This can help us organize equipment in the planning phase; it also enables us to create reports about equipment costs later on. When we’re planning for equipment usage and costs, this information can come in very useful, especially for recurring (or very similar) events. The attributes in the required table are:

  • show_id and equipment_id – Refers to the related show and equipment. This pair forms the UNIQUE key of the table.
  • quantity – The quantity of that equipment needed.
  • cost_planned and cost_actual – What we expect to pay for installing or renting equipment and what we actually paid.

Section 3: Employees

Employees subject area

The subject area of this model is about employees and their roles. I always love to point out that people and their time are the most important part of any project. Anything else is just a tool to do a job. (And that tool was also made by people, using their time. ☺ )

I won’t explain the employee, role and has_role tables here. I’ve done it many times before, for instance in this article. If you need to, please review it.

The final table in our model relates employees and roles with shows. We can expect to have a limited number of qualified employees and we’ll need be sure that they will be available when needed. Obviously, the same person can’t be in two different places at the same time. The attributes in the engaged table are:

  • show_id and has_role_id – References the related show and employee role.
  • start_time – When we expect an employee to start that role.
  • end_time – When that role ends. This is nullable because in most cases we’ll assign a value after the employee has finished their role. However, we might enter an expected end time here.
  • cost_planned and cost_actual – What we expect to pay an employee for handling that role and what we actually paid.

Once again, I’ll just point out that this historical data can be very helpful when you’re organizing a repeat event or one that’s similar to a past event.

Today we’ve discussed a possible data model for an event management database. We’ve covered the really important things, like describing the event, scheduling performers, and assigning employees and resources to the event. The handling of costs in this model is simplified, but it still provides us with the ability to calculate planned and actual costs by category, event, show or equipment type.

I’m not an event manager. If you are, I hope you’ve found this article very helpful. But I would like to hear your feedback on what additions or changes could be useful in real-life situations.

Of course, everyone is welcome to submit their suggestions and ideas in the comments section.

 
 

Try our online database modeler. No registration. No commitments.

 
 
Tags
 
Subscribe to our newsletter

If you find this article useful, join our weekly newsletter to be notified about the latest posts.

 
 
 
New SQL Course! The only interactive course for SQL window functions on the Internet. View course Discover our other courses: