I am working on a solo AWS project for practice/learning. I've never worked with DynamoDB before so I was just wondering if I could have some guidance regarding my DyanmoDB Schema to ensure that I am on the right path before I begin my project. I am not sure if I am utilizing DynamoDB's capabilities correctly, so I'd love to hear your feedback. Any input at all would be greatly appreciated.
The project is simple, I am making a task management application (users can create, manage, and track tasks. The application will support user authentication, task management (create, read, update, delete), and notifications for task deadlines and completions.)
The DynamoDB schema I have come up with so far is as follows
I have read that it is better to use fewer tables, so I'm wondering if it would be better to combine some of my tables or if it the current implementation is better? (Ex: combine my users and tasks table into one and have a generic partition key called PK with values such as USER_<user_id> and TASK_<task_id> and depending on the PK value there are different attributes for the item?)
Users table:
- UserID: Partition key, UUID (Not sure if a userId is necessary; should email be the partition key instead?)
- Email: Sort key
- Password, FirstName, LastName, PhoneNumber
- NotificationPreferences: JSON/Object (DynamoDB Map type) field storing user's notification preferences (email and sms are defaulted to false. The default will be in-app notifications and then the user can opt for more options)
- CreatedAt, LastLogin (Timestamps)
When user accounts are being created, I will be querying the database to ensure that no other user with that email exists
- Global Secondary Indexes (GSIs): EmailIndex
Tasks table:
- UserID: Partition key referencing the UserID from the Users table, (chose this as partition key so each user's tasks will be stored together for efficient retrieval)
- TaskID: sort key, time-based UUID (need UUID to prevent duplicate primary keys)
- Title, Description
- Status: enum (in progress / completed)
- NotificationSent: Boolean flag indicating whether a notification has been sent for this task.
- DueDate, CreatedAt, UpdatedAt, CompletedAt (timestamps)
An AWS Lambda function will be run everyday to efficiently query all tasks where NotificationSent is false regardless of the UserID to publish to SNS topics to send out sms/emails
- GSI (Global Secondary Index): NotificationSentIndex
- Partition Key: NotificationSent
- Sort Key: DueDate
When users go on the homepage, I will be querying the tasks for each user and have it sorted by the most recently updated tasks
- LSI (Local Secondary Index): UpdatedAtIndex (since we are querying items within the same partition key as original table)
- Partition Key: UserID
- Sort Key: UpdatedAt
Notifications Table
- UserID: Partition key referencing the UserID from the Users table, (chose this as partition key so each user's notifications will be stored together for efficient retrieval)
- NotificationID: sort key, time-based UUID
- TaskID referencing the TaskID from the Tasks table, indicating the task associated with the notification.
- CreatedAt: Timestamp indicating when the notification was generated.
- isRead: Status of the notification showing if the user opened the notification in the app (true/false).
When users click on their notifications tab in the frontend, we will be querying the notifications for each user and have it sorted by when they were received
- LSI (Local Secondary Index): NotificationTimestampIndex (with ScanIndexForward=False)
- Partition Key: UserID
- Sort Key: CreatedAt