Introduction

This is the second post in the “Modeling Google Calendar” series. See the first post here: https://databasedesignbook.com/posts/database-design-for-google-calendar-pt-1 for introduction, problem description and the first part, “Basic all-day events”.

This series illustrates the database design approach explained in the book called “Database Design using Minimal Modeling”, scheduled to be released in Spring 2024. Here is the website of the book: https://databasedesignbook.com/. You can subscribe to receive updates on the book and to get notified of new posts in this series.

Part 2: Time-based events

In the previous chapter, we discussed basic non-repeating date-based events. Let’s see how our modeling approach would handle time-based events.

We modeled date-based events as the DayEvent anchor with the following attributes:

• What is the name of this DayEvent?
• When does the DayEvent begin?
• When does the DayEvent end?

Also, we defined a link between User and DayEvent: “User creates many DayEvents”.

Let’s write down a quick draft of time-based events and see how it compares to date-based events. Quoting the “problem description” section:

Time zones

Every country and territory in the world uses one or more time zones. Time zone definitions occasionally change. Each country, being a sovereign state, can decide to change their time zone definition.

Time zones may use Daylight Savings Time, or can be uniform. New time zones may be introduced, or retired. In this text, we won’t go into complications of handling time zone definitions. If you were really implementing a serious global calendaring solution, you’d probably have a separate team dealing with such issues.

However, we will implement the full timezone-aware events that are usable in practice.

We have one motivating example: flights. Planes often cross time zone boundaries, and the take-off and landing times in your ticket would be in different time zones. Say, there is a flight from Amsterdam to London that departs on December 24, at 16:50 (Amsterdam time) and lands at 17:05 (London time). The flight duration is 1 hour 15 minutes.

Time zones inspire a lot of programming folklore. There are many blog posts, horror stories, “things every programmer should know” and other texts related to time zones, particularly in database context. We will discuss only as much as needed for our purposes, and briefly mention some important things to consider.

Anchors

Having said that, it seems that we need to add two anchors:

There are dozens of time zones in the world. We can confirm the validity of the Timezone anchor by writing down example sentences. If those sentences make sense then we can be more certain that we've found the anchors:

• “There are 120 Timezones in our database”;
• “When this import script finishes, a new Timezone is added to our database”.

(Timezone data structure is discussed below.)

The sentences for TimeEvent are also straightforward:

• “There are 2500 TimeEvents in our database”;
• “When this button is clicked, a new TimeEvent is created”;

Attributes of Timezone

For the purposes of this text, we’ll do only a very minimal model of Timezone. Basically, the only attribute we’d introduce is:

We won’t go into details of how the timezone is actually defined. We’ll assume that there is a separate database that describes the structure of timezones. Also, we assume that there is some function that takes a local time in the specified timezone and returns UTC time (and vice-versa). This will be discussed in more detail in the next chapter, when we will talk about repeating events.

For clarity, here is what else would be included into a time zone definition:

• What is the UTC offset of this time zone?
• Does this time zone have Daylight Savings Time?
• When does DST begin? When does DST end?
• What is the UTC offset when DST is on?
• We’d also need to model previous definitions of a time zone. For example, the government may decide to change the day when DST goes into force, or get rid of DST, etc.
• Is this time zone active or retired?

This is an incomplete list. Modeling all of this data using our approach is possible, but is a separate, and quite technical exercise.

Let’s get back to events.

Attributes of TimeEvent

Note that we’re using local time here. You may have read that time should be stored in UTC time (without any time zones), and then formatted for human readability using a preferred time zone.

Here we have a different situation, and this advice does not apply. Time zones can change. Suppose that we scheduled a billiard game on September 6, 2058, from 09:30 to 11:00, Cologne time. At the moment we don’t know what UTC offset is going to be then. So we must store the data as entered by the user, and then adjust it as the local legislation changes.

Links

We have two very similar links here:

The definitions of both links differ only by a single word (“start” vs “end”).

Most of the time-based events would have the same time zone for both start and end times. We design this as a general case: we always specify both, even if they are the same. This approach would help us to get used to handling more complicated cases.

Similarity between DateEvent and TimeEvent

All-day events and time-based events look similar. Should we already think about unification?

For example, both events have names. Also, how far are “date” and “date with time” from each other? We can also observe that both types of events would have more data in common, such as location, list of invited guests, description, etc. Maybe we could extract that to some component shared between two anchors?

Thankfully, logical modeling allows us to pause a little bit before making that decision. After all, it’s just simple tables that we can reshuffle before we commit to a physical table implementation.

First, we want to gather more information on similarities (and, more important, dissimilarities) between the two types of events that we’ve seen so far. Also, we want to see if there are more types of events, and what attributes and links they have.

In the follow-up post we’re going to talk about repeated events. You can subscribe at https://databasedesignbook.com/ to receive new posts in this series.
​