Delivering Christmas Presents: Santa’s Data Model

With the holidays quickly approaching, Santa needs some additional help to deliver presents to children around the world. Today, we’ll develop a data model that can help Santa and his elves work more efficiently.

Background

Santa’s job is extremely important, so he needs to do everything he can to ensure on-time success. Just remember all the problems Howard encountered in ‘Jingle All the Way’ when trying to find a single Turbo Man figure—we can’t let Santa slip up again, or his reputation will be ruined. So, to help Santa stay organized, we’ll divide his activities into three main phases.

1. Planning

   First, Santa needs to plan everything. After all, he can’t have his elves running around the factory and panicking as they try to make sense of billions of deliveries! In addition to reading this year’s letters and determining what gifts children would like, we should also analyze any trends from previous years to gather some common materials or even produce gifts ahead of time. This will help reduce some of the backlog as we begin working on production. Santa also needs to coordinate with his logistics team to optimize delivery routes and ensure every gift reaches its destination on time, while elves are ready to design your personalized photo calendar to keep track of the year's festivities.

2. Production

   After the planning phase, we’re ready to begin producing presents. With the help of Santa’s elves, we can quickly manufacture and package presents according to the wish lists we received. To make the process more efficient, though, we need to organize all materials and information we have on hand so the elves can grab the things they need as quickly as possible.

3. Delivery

   The moment is quickly approaching! Santa’s reindeers are all set, and the man himself is anxiously checking his watch. Presents are quickly being loaded into the sleigh by Santa’s helpers. At this point, Santa’s taking a last look at his schedule to make sure he has all the right addresses, as well as any notes he’ll need to consider.

Now that we have a bit of background on the kind of information we’ll need to work with, we can finally begin designing Santa’s data model.

The Data Model

This data model consists of three sections:

1. Items
2. Persons and Wish Lists
3. Deliveries

Let’s take a closer look at each of these.

Section 1: Items

Our data model begins with the Items section, which contains many tables that are central to the remaining two sections.

The item_type table is arguably the most important one here. This table contains a list of all items we’ll need to produce at Santa’s factory. For each item, we’ll store the following information:

• item_name — the name of the item.
• properties — textual key-value pairs denoting the size, shape, color, and other properties of the item produced, stored in a structured format.
• description — an unstructured textual description of the item.

If we ever have two similar items that only differ in some of their properties, such as color, we’ll go ahead and store them as individual records in the table.

For the purposes of our data model, we’ll assume that Santa won’t purchase gifts but will instead order his elves to produce everything in house. For each different item type, we’ll have a list of prerequisites that we must fulfill. These could be labor or materials like wood, plastic, metal, and paints. We’ll need to store a list of all possible prerequisites and relate them to each item we need to produce. We’ll use four tables to achieve this.

The first of these four tables is prerequisite, which, as the name suggests, stores a list of all possible prerequisites. For each record in this table, we’ll store a unique prerequisite name, all additional properties (this time in an unstructured format), and references to the prerequisite_type and unit dictionaries. The prerequisite_type dictionary will be used to store a list of all prerequisite categories, such as “labor” and “materials”. The unit dictionary will be used to store a list of all units we’ll use to quantify our prerequisites. For example, we can expect labor to be measured in hours or minutes and materials to be measured in terms of cost of production (dollars), weight (kilograms), or volume (liters).

The last table in this section is warehouse, which we’ll use to track the current status of our inventory for both items and materials (hence the item_type_id and prerequisite_id foreign keys). Only one of these two keys will contain a value at any given point in time. In addition to these keys, we’ll store the final quantity that was available on a particular warehouse_date.

Section 2: Persons and Wish Lists

A critical part of our data model, this section deals with the things children want to find under their Christmas trees! We’ll work from right to left.

The two rightmost tables are country and city. We’ll use these two tables when referencing the location of a child who sent Santa a wish list. The country table contains only the unique country_name attribute and a list of all existing countries. To be more precise with our locations, we’ll use the city table to store all cities Santa will need to visit. For each city in this table, we’ll store:

• city_name — the name of the city, which isn’t necessarily unique.
• postal_code — the postal code of the city.
• country_id — the id of the country the city is located in. Together with the previous two attributes, this forms the unique key for this table.
• latitude and longitude — used to help Santa find the city on his map or to enter its coordinates into the navigation system he uses.

Of course, you can’t have wishes without people! We’ll store a list of all people in the person table. For each individual, we’ll store a first_name, last_name, birth_date, and city. We’ll also store the address of the person, as well as any additional delivery_details Santa may need to consider (such as a note indicating that a person doesn’t have a chimney).

The last table of this section contains the full wish_list that stores all Christmas wishes ever made. For each wish, we need to know:

• person_id — a reference to the person who made the wish.
• item_type_id — a reference to the item (type) the person requested.
• quantity — the desired quantity of the item specified in the wish.
• details — all details that can help Santa fulfill the wish.
• ts — denotes the moment when the wish was stored in our system, which is important for determining the year in which the wish was made.
• gift_id — a reference to the gift table denoting the gift that was delivered to fulfill this wish.

Section 3: Deliveries

Now, we’ve finally arrived at the most interesting part of our data model—gifts and deliveries!

After a single item is produced, we’ll insert its related record into the item table. Note that when an item is produced, it’s still not assigned to any gift, so the gift_id attribute will contain a value of null until the item is associated with a particular gift. We’ll also need to store the type of item that was produced (item_type_id), as well as its quantity. While an item’s quantity will mostly be 1, we can expect different quantities in some special cases (e.g., more than 1 item combined into a set—this is very unusual but nonetheless possible).

Moving on, we’ll combine one or more items to produce a gift. We’ll update item.gift_id once we’ve packed our selected items into that gift. Each gift will be delivered to its related person (person_id) and will have a tracking status (current_status_id), as well as a timestamp of when Santa plans to deliver the gift (delivery_time_planned). We’ll also update the value of the wish_list.gift_id attribute for all items that were successfully delivered.

The last two tables in this data model concern tracking delivery statuses. First, the status table contains a unique status_name value that we’ll use when referencing the current status of the gft (gift.current_status_id). Additionally, the status_history table will store a list of all statuses for all gifts in our database, as well as the timestamps of all status updates (ts).

Hopefully, our data model will help Santa complete yet another successful year of deliveries so we can all receive our presents on time. If you’re in the mood for more Christmas-themed SQL, Vertabelo Academy has prepared a special 24-query holiday challenge. Go ahead and check it out! On behalf of the Vertabelo family, we hope you have a wonderful Christmas!