I've been working on new projects and experimenting with DynamoDB single-table design and recently I've encountered some problems with implementing more complex use cases and would like to get some input.
I was wondering how you implement resources that have multiple relations in DynamoDB. I understand you need to think about access patterns when designing your hash and sort keys, but I am struggling with scenarios where there are multiple ways in which the data will be accessed. Here is an example scenario with job postings and its access patterns:
Here is what I came up with
| PK (Hash Key) | SK (Sort Key) | Title | Publish Date | ... |
|---|---|---|---|---|
| COMPANY#12345 | COMPANY#META | Microsoft | ... | |
| COMPANY#12345 | JOB#7890 | Developer | 2023-03-28 | ... |
With something like this, I can see how you could get all jobs for a company:
PK = 'COMPANY#12345') and begins\_with(SK, 'JOB#')Get all jobs:
begins\_with(SK, 'JOB#')Get a single job (assuming you have the id) with a GSI that swaps PK and SK
SK = 'JOB#7890'Past these three access patterns, I'm not sure I see what the best way to go would be. I would appreciate if anyone has some tips on how things could be re-designed to permit all the access patterns above.
Get jobs by certification required (ex: Forklift certified)
This seems like a FilterCondition on an existing access pattern.
Get jobs by date published
Will require a GSI with a static variable as PK and data as SK, or if you wanted to list jobs by company by date you could do the following, allowing you to list job by company ordered by date:
| PK (Hash Key) | SK (Sort Key) | Title | ... |
|---|---|---|---|
| COMPANY#12345 | COMPANY#META | Microsoft | |
| COMPANY#12345 | 2023-03-28#JOB#7890 | Developer | |
| COMPANY#12345 | 2023-03-25#JOB#0090 | Developer | |
| COMPANY#12345 | 2023-01-22#JOB#3390 | Developer |
Get jobs by status (ex. statuses: "taking applications", "reviewing applications", "complete")
Will require a GSI