Design a relational model for a calendar application
Design a relational model for a calendar application. The application will support users adding events with a specific time and time zone. Reminders about upcoming events will be pushed to users and have the ability to invite other users to events.
In designing the relational model for a calendar application, several core entities and relationships need to be defined. The main entities will be Users, Events, Reminders, and Invitations. Each user can create and participate in multiple events, and events can have multiple users invited. Thus, there is a many-to-many relationship between Users and Events, which can be modeled using a join table, likely called EventParticipants. This join table will store foreign keys to both the Users and Events tables, representing which users are invited or attending. Additionally, the Events table will store the event’s specific time, time zone, and possibly other metadata like description, location, and recurrence rules.
Reminders can be linked to events through a one-to-many relationship since each event can have multiple reminders. The Reminders table would reference the Events table via a foreign key and store data like the reminder time (relative to the event time) and the type of reminder (email, notification, etc.). Invitations for events, similar to reminders, can also be modeled as a separate table (one-to-many relationship) with references to both Users and Events, indicating which users have been invited and their RSVP status.
To efficiently query this data, especially for common views like showing a user’s upcoming events or reminders, indexes will likely be added on fields like user ID, event time, and reminder time. Caching strategies can also be employed to store upcoming events or frequently accessed data to reduce the load on the database, particularly for read-heavy queries such as viewing a user’s schedule or reminders.
Related Problems
A video service (like youtube) has many viewers watching videos. Given a stream of the video IDs that are being watched, we need to find the top K most viewed videos for different periods of time (1 hour, 1 day, 1 month, all time). For the top K videos returned, we also want the count of views during this period.
Design an app like google maps. The app should provide the quickest possible route between two arbitrary locations. It should provide an ETA, estimated time to reach a destination, using current traffic data.
Sending user notifications is a common requirement in system design. Design a notification service for an organization. The system will use shared services for the underlying messaging implementation (email, sms, push notifications, etc) so the actual messaging implementation does not need to be designed. The system should support a user publishing a notification to a single user or groups of users. Notifications can be triggered manually via a web UI or programmatically via an API. Users should be able to view their past notifications they published. If a user is unable to receive a notification, they should still receive it at the next opportunity and not miss the message. The notification service should scale to billions of notifications per day, with messages delivered within a few seconds, with five 9s uptime.
Design a url shortener service (similar to tinyurl).
1. Generate expiring unique short URL from provided URL
2. Redirect users to the correct website when they navigate to the short URL