Problem and Scope
Design a relational database model for a calendar application that allows users to manage their events, reminders, and invite other users to events.
Start by confirming the core user journey, exclusions, success criteria, and the constraints that materially affect the architecture.
Requirements to Clarify
A strong answer should establish scope before choosing components.
Functional requirements:
- Users should be able to create and manage calendars
- Users should be able to add events to their calendars
- Events should have start and end times or can be all-day events
- Users should be able to set reminders for events
- Users should be able to specify their time zone
- Users should be able to invite other users to events
- Multiple users should be able to view and interact with shared calendars
Non-functional requirements:
- The database schema should be normalized to avoid redundancy
- The schema should support efficient querying for common operations
- The design should scale to handle a large number of users and events
- The model should maintain data integrity across relationships
Scale and Capacity
Use the workload to justify storage, partitioning, caching, and reliability decisions. Clarify or challenge these assumptions rather than treating them as unquestionable facts:
- A user might have multiple calendars (work, personal, etc.)
- A user might have hundreds of events in their calendars
- An event might be shared with multiple users
- Reminders need to be sent through various channels (email, SMS, etc.)
Architecture Discussion
Walk through the important read and write paths, identify ownership boundaries, and explain how the design behaves when dependencies fail. Cover these areas explicitly:
- User table to store user information
- Calendar table to represent different calendars
- Event table to store event details
- Relationship tables to manage the connections between users, calendars, and events
- Reminder/notification table to handle event reminders
- Tables to manage notification preferences and channels
- Designing appropriate relationships between users, calendars, and events
- Handling time zones properly
- Managing permissions for shared calendars and events
- Creating a schema that supports efficient querying for common operations
- Ensuring data integrity across relationships
Follow-up Questions
Expect the interviewer to test the consequences of your choices. Practise answering these questions with a concrete decision, its benefit, and its cost:
- How would your design handle shared calendars where multiple users have different permission levels?
- Focus: Assess the candidate's answer to "How would your design handle shared calendars where multiple users have different permission levels" for explicit assumptions, a workable mechanism, failure behavior, and consequential trade-offs.
- How are you thinking about storing and displaying events across different time zones?
- Focus: Assess the candidate's answer to "How are you thinking about storing and displaying events across different time zones" for explicit assumptions, a workable mechanism, failure behavior, and consequential trade-offs.
- What indexes would you create to optimize common queries in your schema?
- Focus: Assess the candidate's answer to "What indexes would you create to optimize common queries in your schema" for explicit assumptions, a workable mechanism, failure behavior, and consequential trade-offs.
- How would your model scale if we needed to support team calendars with many users?
- Focus: Assess the candidate's answer to "How would your model scale if we needed to support team calendars with many users" for explicit assumptions, a workable mechanism, failure behavior, and consequential trade-offs.
- What are the trade-offs in your chosen approach for handling notifications?
- Focus: Assess the candidate's answer to "What are the trade-offs in your chosen approach for handling notifications" for explicit assumptions, a workable mechanism, failure behavior, and consequential trade-offs.
Evaluation Rubric
MockMe evaluates the answer across the following dimensions. A complete answer should connect claims to requirements and explain consequential trade-offs.
- Requirements and scope (15%): Clarifies and prioritizes the required behavior for Users should be able to create and manage calendars; Users should be able to add events to their calendars; Events should have start and end times or can be all-day events. Establishes the constraints that materially affect Calendar App Database Design, including The database schema should be normalized to avoid redundancy; The schema should support efficient querying for common operations. Strong evidence includes Separates the critical path from secondary features and resolves ambiguous requirements before choosing components.
- Architecture and interfaces (20%): Presents coherent ownership boundaries and end-to-end flows covering User table to store user information; Calendar table to represent different calendars; Event table to store event details; Relationship tables to manage the connections between users, calendars, and events. Strong evidence includes Defines interfaces and traces important success, retry, and failure paths across the proposed components.
- Data and scaling (25%): Uses workload assumptions such as A user might have multiple calendars (work, personal, etc.); A user might have hundreds of events in their calendars to justify capacity and partitioning decisions. Explains the data, state, or model strategy for User table to store user information; Event table to store event details; Ensuring data integrity across relationships. Strong evidence includes Quantifies a dominant workload, identifies the first bottleneck, and explains how the design evolves as that workload grows.
- Reliability, correctness, and safety (20%): Explains concrete failure behavior, recovery, and operational safeguards for The database schema should be normalized to avoid redundancy; The schema should support efficient querying for common operations; The design should scale to handle a large number of users and events. Strong evidence includes States the required correctness or consistency boundary and covers retries, partial failure, observability, and safe degradation.
- Communication and trade-offs (20%): Drives a structured discussion and compares consequential alternatives for Managing permissions for shared calendars and events; Creating a schema that supports efficient querying for common operations; Ensuring data integrity across relationships. Strong evidence includes Makes assumptions explicit, answers the question asked, and explains both the benefit and cost of major decisions.