Blog

Database Modeling for an Event Ticketing Application

May 6, 2014    Rails Database

One of my readers sent me a description of an upcoming project. I'm going to walk through this example of how I'd model the database.

Before I start, I want to talk briefly about what I’ll be doing. I’m going to walk through the process of coming up with a sketch, or a starting point, for the data model for this application. I like to start this process a couple days before I begin a project. Any application can be modeled many different ways, so I like to have some time to ponder what I’ve come up with before starting.

Like I said, this is just a starting point. When you start development, this data model will change! Don’t worry about that — just use it as a guide as you begin the project.

Naming

Naming is extremely important. Before you start the project, you’ll want to discuss as much as possible with the product owner. Try to name your models and relationships in the same way that they describe things to you. This makes things a lot easier in the long run, as the code corresponds closely with discussions about the application you’re building.

If you are making your own product, perhaps try to write a textual description of it. This will allow you to see the words that you use to describe the models and their relationships. It’s worth taking some time to do this (not too much — but don’t skip over it completely).

The Application Description

Here’s the description of the application. I’ve omitted some of it for the sake of brevity:

So the app is basically an event creation, registration, and invoicing system.

Starting with administrators: an admin could login, create an event, (with a name, start date, registration open date, etc.). This event would have several “levels”. So if a registrant was in 8th grade, they would register as a 6 - 8th grade level, at $XX and have $XX deposit. Other levels could be like an adult going with the group would register as an adult at $XX and have $XX deposit. An event could have unlimited “levels” as I would call them.

A group leader could login to the system, create a group for XX event, put in how many participants at which levels, save it all, get an invoice for deposit, pay that invoice either online […] or by mail. They would receive confirmation emails on the group and be able to manage the number of registrants for which level.

It’s likely that I don’t have a complete understanding of the application that he’s describing (as I’ve only discussed it in two emails). For this example, that’s OK - we’ll imagine that the above description has been extensively discussed and is as complete as possible.

For this post, I’m assuming that the only users are Admins and Group Leaders. Individual people may not register themselves for events. They must be registered as part of a group, by the group leader. This will simplify the model a bit. In a later post, I may expand the model so that single users can register themselves.

Getting started - Users

Typically, I’ll start by just taking notes in a text file, so that I can just get some ideas down. The description starts with the concept of an administrator, so let’s start there.

Since we have only two basic roles, and we’re not completely sure if there will end up being a third, I’m just going to start with a generic User model and a very simplistic role system. Also, I know that Group Leaders are able to create many groups, so I’ll include that now as well.

user.rb
1
2
3
4
5
6
7
8
class User < ActiveRecord::Base
  has_many :groups

  # columns
  # name: string
  # email: string
  # role: string    # this will be text “admin” or “group_leader”
end

Our choice of has_many here will mean that we need a user_id column in the groups table. With that, we’d access the group leader like so: group.user. However, this very descriptive. A group record may end up being associated to a user record multiple times.

It would be much nicer if we could say group.leader, and use a leader_id column in the groups table. We can set that up in our model with the as option:

user.rb
1
2
3
4
5
6
7
8
class User < ActiveRecord::Base
  has_many :groups, as: :leader

  # columns
  # name: string
  # email: string
  # role: string    # this will be text “admin” or “group_leader”
end

Groups

With that set up, we can now see that we need a Group model. Per the application description, a Group Leader will create a group to register people for an Event. I’m assuming that groups are used only for one Event. You cannot re-use a group at multiple events.

group.rb
1
2
3
4
5
6
7
8
9
10
class Group < ActiveRecord::Base
  belongs_to :leader, class_name: 'User'
  belongs_to :event
  has_many :registrants

  # columns
  # event_id: integer
  # leader_id: integer
  # name: string
end

As I said above, a Group will be used for only one Event, so we have a simple belongs_to relationship there. Also, the group belongs to a leader. This is the inverse relationship of the one we defined on the User model.

Finally, from the description, we can see that a group has many participants. The description used both the words “registrant” and “participant” when describing the group members, but I’m going to choose the name “Registrant”.

Here’s what the diagram looks like so far:

Registrants

Because I’m assuming that individual users cannot register themselves, I’m going to create a model separate from the User model called Registrant. I’m doing this because registrants won’t need to log in or anything, so it would be overly complicated to attempt to use the User model for both in this case.

registrant.rb
1
2
3
4
5
6
7
8
class Registrant < ActiveRecord::Base
  belongs_to :group

  # columns
  # group_id: integer
  # name: string
  # email: string
end

Registrants will belong to a Group, and they will have some additional data about the person’s name and email address.

Events

As we said above, a group leader forms groups for an event. It only makes sense to introduce an Event model now. It might look something like this:

event.rb
1
2
3
4
5
6
7
8
class Event < ActiveRecord::Base
  has_many :groups

  # columns
  # name: string
  # start: datetime
  # registration_open: datetime
end

Per the description, we’ve got start, registration_open, and name columns.

The model so far

Let’s take a break and see where we’re at.

So far, we’ve got the User, Group, Registrant, and Event models and most of their associations worked out. We still have a couple of concepts from the description that we haven’t tackled yet. Namely, “payments” and “levels”.

Cost Levels

A "cost level" is how we'll differentiate the types of registrants (1st Grade, 2nd Grade, Parent, etc). The cost and deposit will be different based on the level.

A CostLevel will belong to an event, and the admin will create cost levels for the event. When a group leader adds registrants, they will need to select a cost level for the registrant (i.e., are they in 1st Grade or 2nd Grade?).

I added the "has_many through" association there, and it may or may not be necessary. It would allow us to query for all of the groups that have a registration from the particular cost level.

cost_level.rb
1
2
3
4
5
6
7
8
9
10
11
12
class CostLevel < ActiveRecord::Base
  belongs_to :event
  has_many :registrants
  has_many :groups, -> { uniq }, through: :registrant

  # columns
  # name: string
  # cost: integer
  # deposit: integer
  # event_id: integer
  # registrant_id: integer
end

Here’s the domain model after we add the concept of cost levels (as you can see, I've gone back and added the cost_levels associations to other models):

Payments

Payments models can vary significantly based on the payment processor you're using and how much information you want to keep around.

A very minimal setup for this example might be a Payment model that belongs to a group. With that, you could keep track of how much each group has paid.

payment.rb
1
2
3
4
5
6
7
class Payment < ActiveRecord::Base
  belongs_to :group

  # columns
  # group_id: integer
  # amount: integer
end

Based on the group, we know who paid (the leader) and such. Also, it's important to know when payments occur, but Rails takes care of this for us (mostly) with the automatic created_at and updated_at fields.

Here's the final domain model sketch (for now):

Thanks for reading!

In the next post, I’ll be continuing with this case study, and showing some example queries that we can accomplish (since they may become quite complex for this domain model).

I’ll demonstrate how to approach some of the potential pages, such as the invoice and event summary pages. I’ll also be showing how we can reduce the number of SQL queries needed for said example queries.

If this sounds interesting, please be sure to sign up for the newsletter below, and I’ll email you when that post is completed!