Introduction

This is the third post in the “Modeling Google Calendar” series. Previously:

• ​https://databasedesignbook.com/posts/database-design-for-google-calendar-pt-1 for introduction, problem description and the first part, “Basic all-day events”;
• ​https://databasedesignbook.com/posts/database-design-for-google-calendar-pt-2 “Time-based events”, and some discussion of timezones;

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 about new posts in this series.

Part 3. Repeated all-day events

As we may remember from the initial problem description:

• “Both all-day and time based events:
  • Can be repeated daily, or every N days;
  • Can be repeated weekly, on certain days of the week; again, it can be every two or more weeks;
  • Can be repeated monthly, on a certain day or day of the week;
  • Can be repeated annually;
  • Repeating events can go on either forever, or until a certain date, or for a certain number of repetitions;”

In this post we will talk only about all-day events. In the follow-up post I’m going to show how the minimal modeling method handles commonalities between different anchors, in this case time-based events. Also, we’ll see how the logical schema gets changed: we would use this as an example of how the draft design is edited when a better design approach is introduced. Remember that all of this happens before we begin thinking about database tables, so this is a very smooth process: you don’t need to worry about table migrations yet.

If you think about the bullet points listed above, your reaction may be: “we probably need JSON for that”. That may be true, but JSON belongs to the physical table design, so we won’t discuss this at this point. We’ll design everything that is needed on a logical level, and later on we’ll see what physical options we have.

Attribute #1, cadence

So, let’s ask the first question, hoping that it would help us find an attribute: “How often is that event repeated?” Possible answers to that question: a) never; b) daily; c) weekly; d) monthly; e) annually.

We say that such attributes have an “either/or/or” type. Let’s write it down as an attribute.

For either/or/or attributes, we show the entire list of possible values in the “Example value” column.

What do we do with non-repeating events? On the logical level, an attribute can either be set to a specific value, or unset: this is a basic principle of minimal modeling. So if this attribute is not set then the event would be non-repeating.

Attribute #2, tangled attributes

Reading further, we see that for all four frequencies, there is an additional possibility. The events can repeat every N days, N weeks, N months, and N years. Let’s write that down.

This is a first example of entangled attributes. Its value only makes sense when another attribute is set. We specify that by adding “For repeated events:” as part of the question.

Note that this is only a human-readable notation, we’re not going to discuss here how to write a machine-readable logical schema.

Attribute #3

When you specify a monthly event, you have two options: repeat on the same day of the month (say, every 16th of the month), or repeat on the same weekday of the month as the original date (say, every second Tuesday). The base date is taken from the normal “When does the DayEvent begin?” attribute that was discussed in the beginning.

Let’s try and define this attribute:

Days of the week: micro-anchors

Events can be repeated weekly, on certain days of the week. So, for example we can have a weekly event that happens on Mondays, Wednesdays and Fridays. Where do we store this?

Let’s start with an attribute that may look like this:

Would it work? Modern database systems such as Postgres and MySQL natively support storing arrays: Postgres has an array type, also they both have JSON type, so you can store an entire array in a single table column. Even though we discuss the logical level, this parallel can help to at least confirm the plausibility of this approach. Also, it’s entirely possible that this exactly is how we’ll later decide to store that information, when we will discuss the table schema.

However, the approach taken by Minimal Modeling demands that we introduce a new anchor called DayOfTheWeek.

This is an example of a well-known anchor, similar to currencies, languages and countries. We discuss well-known anchors in https://minimalmodeling.substack.com/p/currencies-countries-and-languages.

DayOfTheWeek may look a bit awkward because it is so tiny. There are only seven possible IDs here, and new ones will never be added. But introducing it helps keep the modeling approach simple.

You can also introduce attributes for this anchor, such as human-readable names of the days of the week.

Now back to our task. We have a DayEvent anchor, and a DayOfTheWeek anchor. To connect them, we need a link.

This link is tangled, like the attributes we’ve seen above. It only makes sense when it corresponds to a DayEvent that is repeated weekly.

Are we done with this?

How can we make sure that we’re done with modeling? Let’s revisit the original requirements again, and highlight the parts that we’ve covered so far:

• Both all-day and time based events:
  • Can be repeated daily, or every N days;
  • Can be repeated weekly, on certain days of the week; again, it can be every two or more weeks;
  • Can be repeated monthly, on a certain day or day of the week;
  • Can be repeated annually;
  • Repeating events can go on forever, until a certain date, or for a certain number of repetitions;

Okay, we can now see that we forgot about the number of event repetitions. The corresponding part of requirements is not marked in any way. Let’s fix this.

Repeat limit: more tangled attributes

“For how long do the periodic events repeat?” This looks like a plausible sentence to define the attribute. There are three possible answers: “forever”, “until a certain date”, and “for a certain number of reps”.

Now we can add two final missing pieces: which date, and how many repetitions.

We’re done.

Let’s quickly summarize the pieces of data that we defined here:

Anchor: DayEvent

• attr: How often is it repeated?
  • attr: (for monthly) Which day does it fall on?
  • link: (for weekly) falls on certain DaysOfTheWeek;
• attr: What is the repetition step?
• attr: For how long is it repeated?
  • attr: (until a certain date) When does the repetition end?
  • attr: (for number of reps) How many times is it repeated?

Anchor: DayOfTheWeek

In total, one extra anchor; six attributes, some of them tangled; one link.

What’s next?

In Part 4, we’ll discuss an interesting topic: how exactly are we going to render the calendar page to the user, say for a certain week or month? How do we find which events fall on certain days?

You can subscribe at https://databasedesignbook.com/ to receive new posts in this series.

​