Previously in the series

This is the fifth 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”;
• ​https://databasedesignbook.com/posts/database-design-for-google-calendar-pt-4 “Rendering calendar page: 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 late 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.

First we’re going to cover time-based events. Then we’ll summarize everything we’ve had so far, and this may be the most interesting part.

Part 5. Rendering the calendar page: time-based events.

For repeated time-based events, we choose the same approach as with all-day events. We’re going to introduce an anchor called TimeSlot. TimeSlot corresponds to a specific event on a specific date and time. One repeated event corresponds to several TimeSlots.

Specific time slots can be moved or canceled, just like the all-day slots.

Here is the anchor:

And the attributes:

A specific time slot can generally even be moved to a different day, so we have to keep this information also. Which time zone shall we use for the begin/end time? As you may remember from Part 2, in Google Calendar you can have different time zones for begin and end time. If you think about that, it makes sense to keep it for the time slots also.

Also, we need to connect TimeSlots with TimeEvents, same as we did with DaySlots/DayEvents:

Part 6. Complete logical model so far

The complete list of anchors:

List of attributes (ordered by anchor):

List of links (in no particular order):

Graphical schema

Finally, here is a graphical schema that shows all anchors and links (but not attributes):

​
​

Next: Creating actual SQL tables

In the final post of this series we’re going to create database tables. This procedure is completely straightforward. It’s possible that you, the reader, can already do that on your own if you had any exposure to database basics, based on the business-level objects that we've discussed so far.

We have 7 anchors, 21 attributes and 10 links so far. We’ll use the one of the most simple table design approaches: one table per anchor. Based on that, we’re going to have 7 + 2 = 9 tables (number of anchors plus number of M:N links), and 21 + 8 = 29 fields in total (number of attributes plus number of 1:N links).

If the tables above correctly describe the business requirements then the SQL tables will be correct. We’ll talk about evolving requirements in the book. Also, we’ll discuss design mistakes and how to fix them.

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