E-Calendar: High Level Design

We will design a Google Calendar which is a popular online calendar service that allows users to create, edit, and share events. It is a good problem to assess the knowledge and problem solving skills of a senior developer. There are many challenges one has to think about before looking into the architecture of the system. This is generally a good rule of thumb while solving problems of software to write down the requirements before thinking of a solution, as each problem requires a slightly different solution.

We can start by listing the functional needs of such a system.

• Users can create events of different types, such as meetings, appointments, and reminders. Each event has its own properties, such as a start time, end time, location, and attendees.
• Users can search using various parameters. Events can be filtered by date, time, location, and attendee. They can also view events in a variety of ways, such as by day, week, or month.

Complete list of features

For the sake of completion, I thought what would be all the system requirements and noted them here although we are working only on the first two problems in this article.

• Create, edit, and delete events
• Share events with other users
• Search for events
• View events in a variety of ways
• Receive notifications about events
• Invite users to events
• Respond to invitations
• Manage availability
• Collaborate with other users

Database Model

Some common tables that are used in calendar systems include:

Users: This table stores information about the users of the system, such as their name, email address, and password.

Events: This table stores information about the events in the system, such as the start time, end time, location, and attendees.

Permissions: This table stores information about the permissions that users have to view and edit events.

Notifications: This table stores information about the notifications that have been sent to users.

Here is a sample simplified question: You have been given a Calendar view with 3 events.

a) Breakfast : on 10 July 2023 which is a Monday from 10–11AM

b) Lunch: on every Tuesday from 2–3PM starting from 11July

c) Snacks: one every Friday from 7th July to 14th July from 4–5PM

Design a system which can store these events and return results if the user queries for an event on a particular day and time.

Before thinking of a solution let us write down the functional requirements of this problem.

a) Handle single events

b) Handle recurring events which can be scheduled on a weekly basis and are no longer than 1 day event.

As you see in a 45 minute interview the scope of the problem is simplified. But we should ask these questions, this shows our analytics skills.

Solution 1:

The database model I inititially came up with is:

Events table:

id — serial

title — varchar

starttime — timestamp

endtime — timestamp

Events Rules table:

events_id — int

day_of_week — string

Here, I have two tables where the 1st table stores the meta of each event data and the second table will store their occurrences.

To me this had a clear distinction between event data and any rules related to the events. Let me know what your first attempt is ?

Problems

As you may notice there are few followup questions an architect will question you based on your initial draft.

• How can we optimise our queries since it requires us to query two tables which have 1–1 mapping.

This is a good question as when we take into consideration the scale of the requests, joining is difficult even with indexing as the index size may increase.

Solution 2:

Following is my updated table:

Events table:

id — serial

title — varchar

startdate — timestamp

enddate — timestamp

Start time — string

Endtime — string

day_of_week — string

This model is a cleaner and efficient way to store calendar events.

• We can always find the start and end timing of an event.
• And recurring events have their rules mentioned as day of week in the database.
• The end date of a recurring event is also stored in the database.

Question1:

Now that we have reached a desired database design we should see how our search queries will look like.

Let’s say a user wants to check he has any commitment at 2:30PM on 11 July.

We can build a Node Js service which can handle a request.

• First, we can write a util function for a date and find the day of the week. E.x Since, 11th July is a Tuesday.
• Second, check if the rule exists or is it a single event.

As you can see, to check for a recurring event we checked for these conditions:

1. endtime is null or the last date of the end of the recurring event,
2. starttime if less than the matching time,
3. day of week matches the event date

Following are the queries to run in your terminal:

CREATE TABLE events(
id SERIAL PRIMARY KEY,
title VARCHAR NOT NULL,
starttime TIMESTAMP NOT NULL,
endtime TIMESTAMP,
day_of_week VARCHAR NOT NULL
);

INSERT INTO events (title, starttime, endtime, day_of_week)
VALUES (‘Breakfast’, ‘2023–07–10 10:00:00’, ‘2023–07–10 11:00:00’, ‘MONDAY’);

INSERT INTO events (title, starttime, day_of_week)
VALUES (‘Lunch’, ‘2023–07–11 14:00:00’, ‘TUESDAY’);

INSERT INTO events (title, starttime, endtime, day_of_week)
VALUES (‘Snacks’, ‘2023–07–07 16:00:00’, ‘2023–07–14 17:00:00’, ‘FRIDAY’);

I used an online postgres editor to run this query, so I am sharing the same with you here.

Question 2

The next question the interviewer wanted to know how can I make this query faster.

I used an index for which I picked starttime.

CREATE INDEX idx_starttime ON events(starttime);

Hope you liked this solution. This is a very short intro to questions for an Engineering Manager position. I plan to write more in future revolving my experiences.