Today's task was to design a db schema for the following requirements:

1. user should be able to register
2. user should be able to record a voice note for reminder
3. user should be able to type a note
4. user should be able to schedule the reminder
5. system should be configured for configurable frequency of reminder
6. user can configure reminders for others

First I created this flowchart:

Database Design:

I choose postgresSql for this task:

Database Schema: Users table:

CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL, -- Hash the password
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Notes table:

CREATE TABLE Notes (
    note_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES Users(user_id) ON DELETE CASCADE,
    note_type ENUM('text', 'voice') NOT NULL,
    note_content TEXT, -- For typed notes or voice notes metadata
    voice_url VARCHAR(255), -- URL or path for voice note file
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Reminder table:

CREATE TABLE Reminders (
    reminder_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES Users(user_id) ON DELETE CASCADE,
    note_id INT REFERENCES Notes(note_id) ON DELETE CASCADE,
    scheduled_time TIMESTAMP NOT NULL,
    frequency VARCHAR(50) DEFAULT 'once', -- e.g., daily, weekly
    recipient_user_id INT REFERENCES Users(user_id), -- To set reminders for others
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Reminder settings table:

CREATE TABLE ReminderSettings (
    setting_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES Users(user_id) ON DELETE CASCADE,
    reminder_id INT REFERENCES Reminders(reminder_id) ON DELETE CASCADE,
    frequency VARCHAR(50) NOT NULL, -- Custom frequency configuration
    last_notified TIMESTAMP -- Tracks last reminder sent
);

More changes/ updates to be made as the project scales!!

Test cases:

1. User Registration and Login:

-Successful registration with valid credentials
-Failed registration with invalid credentials (e.g., weak password, duplicate email)
-Successful login with valid credentials
-Failed login with invalid credentials

2. Note Creation and Management:

-Create a new note with title and content
-Edit an existing note
-Delete an existing note
-Search for notes by title, content, or tags

3. Reminder Creation and Management:

-Create a one-time reminder
-Create a recurring reminder with different frequencies (daily, weekly, monthly, yearly)
-Set reminders for notes and independent reminders
-Edit and delete reminders
-Mark reminders as completed
-Snooze reminders

4. Reminder Notifications:

-Receive timely notifications for reminders
-Configure notification preferences (e.g., sound, vibration, email)
-User Profile and Settings:
-Update user profile information (e.g., username, email, password)
-Configure notification settings
-Manage privacy settings

Specific Test Cases as per today's google meet Question:

To test the retrieval and sorting of past reminders:

1. Create Multiple Past Reminders:

-Create a mix of one-time and recurring reminders with various dates and times.

2. Retrieve Past Reminders:

-Use the app's functionality to view past reminders.

3. Sort Past Reminders:

-Sort the reminders by date, time, or frequency.
-Verify that the sorting is correct and consistent.

4. Filter Past Reminders:

-Filter the reminders by specific criteria (e.g., date range, frequency).
-Verify that the filtering is accurate.

latest work

    classDiagram
    class User {
        - userId: String <<PRIMARY_KEY>>
        - name: String
        - email: String
        - password: String
    }
 
    class Reminder {
        - reminderId: String <<PRIMARY_KEY>>
        - userId: String
        - title: String
        - description: String
        - dueDate: DateTime
        - recurrence: String
        - isCompleted: Boolean
        - triggeredAt: DateTime[]
    }
 
    class Notification {
        - notificationId: String <<PRIMARY_KEY>>
        - userId: String
        - title: String
        - message: String
        - createdAt: DateTime
        - isRead: Boolean
    }
 
    User "1" -- "*" Reminder : creates >
    Reminder "1" -- "*" Notification : generates >

Explanation:

1. A User can create many Reminders.
2. A Reminder can generate multiple Notifications, which are sent to the user.
3. The Reminder entity now includes a triggeredAt array to store timestamps of when the reminder was triggered.
4. The Notification entity is simplified to represent a generic notification sent to a user.

new schema daigram:

Complex Schema daigram:

understanding the above fig:

Overall, the diagram represents a reminder system where:

1. Users can create notes and reminders.
2. Reminders can be assigned to specific users.
3. Reminders have a due date and a frequency.
4. Reminders can generate notifications.
5. Users can configure their reminder preferences.

Additional Observations:

1. The ReminderConfigurations entity seems to be for storing user-specific preferences for 2. reminders, such as preferred notification channels or default frequencies.
2. The Frequencies entity defines different types of frequencies (e.g., daily, weekly, monthly) and their respective intervals.

Relationships:

Relationships:

1. User and Notes: One-to-many relationship: A user can create multiple notes.
2. User and Reminders: One-to-many relationship: A user can create multiple reminders. 3 Reminders and Notes: One-to-one relationship: A reminder is associated with one note.
3. Reminders and Frequencies: Many-to-one relationship: Multiple reminders can have the same frequency.
4. Reminders and Notifications: One-to-many relationship: A reminder can generate multiple notifications.
5. User and ReminderConfigurations: One-to-many relationship: A user can have multiple reminder configurations.
6. ReminderConfigurations and Frequencies: Many-to-one relationship: Multiple configurations can reference the same frequency.