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.
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.
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.
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.
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:
- Persons and Wish Lists
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.
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
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
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
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.
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
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!