A Children’s Party Data Model

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
• Party

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.

The country dictionary contains only the UNIQUE country_name value. For each city, we’ll store the UNIQUE combination of postal_code – city_name – country_id.

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 service_name attribute.

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 partner_name. The 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).

The provides table is the final and most important table in this section. For each record, we’ll store:

• partner_id – The partner that provides a service.
• service_id – The service this partner provides.
• city_id – References the city where 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 partner_id – service_id – 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 first_name, last_name, user_name and 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 employee_id – role_id pair will denote the active roles each employee has at that moment.

Section 4: Party

The 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:

• city_id – The city where the party will take place.
• client_id – The client this party is organized for.
• details – A detailed textual description of the party.
• start_time_planned and end_time_planned – The time we’ve scheduled for this party, including setup and cleanup.
• start_time_actual and 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 (address, email, phone, 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 party.
• service_id – References the service included in the party.
• provides_id – References the provider of 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.
• start_time_planned and 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.

The 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 party and 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.
• party_id – The party related to this invoice.
• client_id – The ID of the client being invoiced.
• invoice_amount, discount, vat_amount, 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.