Organizing children's parties is not an easy job: everything has to be perfectly planned and delivered. Otherwise, chaos happens. It’s up to the adults – more specifically, the party planners – to take care of everything and do it properly.
Is there a better way to do this than to organize everything in a database? We don’t think so!
Children’s parties vary a lot. Some are simple, like birthday parties that include just invitations, food (snacks, beverages, and a cake) and maybe a clown or a magician to entertain the kids. Other parties are much more complex. They may require a trip out of town, sleeping accommodations, and many other activities. The more complicated the party, the less room for mistakes. While a clown that’s 10 minutes late isn’t a big deal, no one wants to wait with a group of bored kids for a bus that’s two hours late!
Let’s see what a data model can do to help party planners stay organized.
What Do We Need in Our Data Model?
Let’s assume we run a party planning business. We’ll have a list of services that we offer to customers. These services could be provided by us, or we could use partners (e.g. we hire the clown).
We combine these services and offer them to customers as a party package. Each package has a starting and ending point, or schedule. This includes not just the party itself, but setting up the party and cleaning up afterward. We may also have multiple locations (e.g. a party starts with pizza at a restaurant, then moves to the beach for swimming).
We’ll also need to relate activities with employees, track parties’ progress, and charge for our services. Let’s see how this is done.
The Children’s Party Data Model
Our children’s party data model consists of four subject areas:
Countries & cities
Partners & services
Employees & roles
We’ll present each subject area in the same order it’s listed.
Section 1: Countries and Cities
This subject area contains only two tables. They are not specific to this model, but we’ll use them in other subject areas.
We can expect to operate in multiple cities and maybe even in several countries. Therefore, we’ll need to reference various cities. This will help us track where parties are located and also what services we offer at each location.
country dictionary contains only the UNIQUE
country_name value. For each
city, we’ll store the UNIQUE combination of
Section 2: Partners and Services
Next, let’s detail the services we’ll provide for our clients.
A list of all possible services is stored in the
service dictionary. It contains only the UNIQUE
In this data model, all services are provided by partners. Even when our company actually provides the service, we’ll treat it as a
partner service (and we’re the partner). The partner dictionary will store all partners we work with, including us. For each partner, we’ll store a UNIQUE
details attribute stores any additional details related to that partner using an unstructured or structured format (e.g. using name-value pairs separated by predefined separator).
provides table is the final and most important table in this section. For each record, we’ll store:
partnerthat provides a service.
servicethis partner provides.
city_id– References the
citywhere this service is provided by that partner.
date_from– The date when the partner started offering that service.
date_to– The date when the partner stopped offering that service. This value could be NULL if that service-partner relationship is still ongoing.
details– All additional details related to that service, such as service description, price, etc. We can expect all details will be in a structured textual format, using key-value pairs.
The combination of
city_id – date_from forms the UNIQUE key in this table. When we enter a new record, we should check that it does not overlap with any existing records.
Section 3: Employees and Roles
Before we move to the central and most important part of our model, we need to look at the tables related to our employees and their roles.
The central table in this subject area is the
employee table. For each employee, we’ll store their
password. They’ll use these last two attributes to access our application.
A list of all possible roles is stored in the
role dictionary. Each role is UNIQUELY defined by its
role_name. Roles are related to actions that each employee performs during a party. Therefore, we can expect values like “party manager” or “assistant” here.
Roles can be assigned to employees by way of the
has_role table. The
role_id pair will denote the active roles each employee has at that moment.
Section 4: Party
Party subject area is the central part of this model. We’ll use it to relate tables from other subject areas, and we’ll also have some new information here as well.
The central table here is the
party table. For each party, we’ll store:
citywhere the party will take place.
clientthis party is organized for.
details– A detailed textual description of the party.
end_time_planned– The time we’ve scheduled for this party, including setup and cleanup.
end_time_actual– The actual times the party (and its related services) took place.
price_offered– The price we quoted to organize this party for this client.
time_offered– When the offer was made.
price_paid– The actual amount the client paid for this party.
time_paid– When the payment was made.
Each party is related to a client. We’ve already referenced the
client table, but now we’ll see what is stored there. I went with basic data only:
client_name, contact details (
mobile), and any additional details in textual format.
Each party will also have a list of services associated with it. That list is stored in the
service_included table. For each record, we’ll need:
party_id– References the related
service_id– References the
serviceincluded in the party.
provides_id– References the
providerof that service, as well as the service itself. This attribute can be NULL, as we’ll update it when we select the specific provider.
details– Any additional textual details related to that service in that party.
end_time_planned– The planned times that service should be provided during the party.
We’ll also need to track the progress of each party. We’ll use two tables to do this.
status table will list all possible statuses that could be assigned to a party. For each record, we’ll store a UNIQUE
status_name and three flags:
successful– Did everything go well? Or were there problems with our services?
paid– Has the party been paid for?
final– Is this the final status for this party?
We’ll assign statuses to services by adding new records to the
party_status table. For each record, we’ll store references to the
service tables and the
timestamp when this status was assigned.
The final table in our model is the
invoice table. It is not specific to this model, but we do need a basic structure to store invoices. For each invoice, we’ll record:
client_name– The client’s name at the time the invoice was issued.
partyrelated to this invoice.
client_id– The ID of the
total_amount– The financial details of the invoice.
time_issued- When this invoice was issued or added to the database.
time_paid- When this invoice was paid.
What Would You Do with this Data Model?
This model is pretty straightforward, but I see several ways we can improve it. What changes would you propose? Is there something we could organize differently? Maybe we need to add or remove a feature. Please tell us in the comments.