Introduction

This is the fourth 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;
• ​https://databasedesignbook.com/posts/database-design-for-google-calendar-pt-3 Repeated 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 about new posts in this series.

Part 4. Rendering the calendar page

So far we’ve been looking at the data model from a slightly unusual angle. We have only one ID for each series of events. Ten weekly project status meetings correspond to a single database record. Unlimited number of birthdays of our friend correspond to a single database record. This is not how people think about their week.

Let’s get back to the application that we’re working on: a calendar. We need to show a weekly view of the user's calendar: say, seven days starting from 26th of Feb up to 3rd of Mar. Which events do we need to show on that page? That week, there is going to be one weekly project status meeting out of ten. If the birthday falls on that week we need to show it.

So, we need to write some sort of SQL query that looks like this pseudo-code:

SELECT …
FROM …
WHERE <date> BETWEEN ‘2024-02-26’ AND ‘2024-03-26’;

Maybe this would even be several SQL queries, or even some code in a programming language. If you think about how this query may look like, given the data that we’ve designed so far, you will realize that it’s going to be very complicated: you need to take a lot into account. This is possible, but may quickly become impractical.

A note on the tempo

I’ve been writing this series for a couple of months already. I’ve been thinking about this problem a lot. I have a quite clear understanding of the end goal that I have in mind, I just need to write it down.

But if I were to present to you the complete table design right now, it wouldn’t be useful for our goal. Our goal is to illustrate the design process. So we're doing a step-by-step explanation.

At the same time, I don’t want to present very small incremental changes. That’s because the text is going to be too long and meandering. So we need to find some middle ground.

We started this chapter with a question of how to render a weekly page. I’m going to go ahead and introduce another requirement that our calendar application definitely has: ability to change and cancel only certain events from the series. The book-keeping part of our database model won’t change. But we need to add some anchors, attributes and links for the second part: both rendering and modification.

General idea

We want to introduce a new anchor that would store the information about every event in the series. So, if we have 10 weekly project status meetings, we’re going to have ten rows in some table. Each record would correspond to a specific date (e.g., 2024-02-12, 2024-02-19, etc.).

First, this would make our rendering very simple. It's very easy to find all the events that fall on a specific day. They are already there.

Second, this would allow us to change and cancel certain events in the series. If we have a project meeting at 12:00 normally, but on a certain week we want to move it to 14:00 (or even to a different day), we can do that. Data in the original book-keeping anchor, TimeEvent, that we defined in part 2, won’t change.

Also, if we just want to skip one project status meeting, we can mark this particular day as skipped.

Day slots

First, we have to find a name for this thing. In some cases this may be a challenge. Five minutes ago, having another cup of tea, I realized that a good word for this thing is “slot”.

Also, like before, we’re going to treat per-day and time-based events differently. So, we’re going to have DaySlot and TimeSlot. Let’s discuss per-day slots first.

DaySlot needs a surprisingly small number of attributes:

Note that the user can change the date for a specific slot! So, we see that this requirement is handled cleanly.

Actually, if you think about this, it’s possible that some other information would also be different for each slot. For example, it’s possible that some meetings will take place in a different location. Also, the guest list may change: you can invite extra people. Also, the attendance would certainly be different. We won’t cover this here, because it’s pretty straightforward.

Finally, we need to establish a link between DaySlot and the corresponding DayEvent.

Second, we have an interesting problem with “infinite” events. Suppose that we added our friend’s birthday to the calendar, repeating annually. How many corresponding DaySlots do we need to create? One possible solution would be to choose some arbitrary limit such as 100 years in the future, and create all the slots for that. Other solutions are possible, such as on-demand creation when user requests to show a calendar page for some distant future week.

Also, one thing to consider is that date arithmetics needs a bit of care. How do we deal with birthdays of people who were born on February 29? We will have to decide something. Maybe we’ll prohibit the user from creating such events? Maybe we’ll ask them where to move the slot: backward or forward? This problem also exists for monthly events that happen on the 31st day of the month.

Exercise for the reader: TimeSlots

In the past few weeks I was busy preparing for the “Incremental Documentation for Your Database” presentation, and did not have time for this series. So, I’m going to publish this part now to keep the ball rolling.

Here is an exercise for the determined reader. Try and think how you would model the TimeSlot anchor, its attributes and links. Fill in the tables with anchors, links and attributes. Think about what’s going to happen with time zones.

How far ahead do you need to think?

Sometimes you can create a better design if you consider a more broad set of requirements. We did it in this chapter: we started thinking about rendering the page, but then we also thought about modifying certain events in the series.

Sometimes you can create a better design if you consider requirements independently. In the previous chapters, we looked at time-based and per-day events, and decided to handle them separately.

So far we did not even think about hypothetical future requirements. We’ve only been designing stuff that we know is needed. Can we imagine something that would be nice to have in the future, and design ahead? If you only use known requirements there is a chance of overfitting design for known data points.

At the same time, sometimes people introduce future design considerations that never actually materialize. In this case design may add extra complexity for actual requirements, adding a bit of friction.

There is always a chance of leaning towards one of those traps, or even falling into them.

Logical design based on Minimal Modeling insists on modeling only the parts that we actually know are needed. We can afford that because Minimal Modeling treats changing requirements as a given.

We’ll discuss this aspect of physical design later in the series. We’ll introduce the concept of Game of Tables, and the idea of Date’s Demon.

What’s next?

In Part 5, we’ll cover TimeSlots.
After that, we’re going to revisit our entire schema and look at its graphical representation.

Then we’ll begin the discussion of actual physical table design. (At this point we already did most of the work, so this chapter is going to be pretty straightforward.)

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