I am setting up DynamoDB. Our data is simple, it has four fields:
- UserName
- UserShiftEndDate
- UserProjectCode
- UserActivities
Ideally, we should query the users based on the UserShiftEndDate. None of the fields/combination in our data is unique. I can add an autogenerated ID for each record.
Please suggest me what fields to choose as partition key and sort key or GSI for optimal response time of query based on UserShiftEndDate.
For an example, consider the table as below :
| UserName | UserShiftEndDate | UserProjectCode | UserActivities |
|---|---|---|---|
| UserA | 12/13/2020 | 45 | monitoring |
| UserB | 12/14/2020 | 47 | testing |
| Userc | 12/17/2020 | 45 | monitoring |
| UserB | 12/14/2020 | 45 | testing |
| UserC | 12/15/2020 | 47 | managing |
My query should take the date as an input and should return all the users data with the shiftEndDate later than or equal to the given date.
For example if 12/14/2020 is given as input, then the query should return all the user data except row 1.
We plan to store date in instant form. To make it simple, I mentioned it as date here
In some cases, you might supply only a partition key as the primary key when creating a table. In these cases, you’re limited to data retrieval by the partition key or by returning all items in a table with the scan operation. Creating a table this way is simple, and in some cases, simple is all you need.
However, as your dataset grows, table scans can become a costly burden in terms of price and performance. Table scans can quickly exhaust your reading capacity and thus increase your bill.
So, Adding a sort key to a table opens more data retrieval functionality beyond scans and partition keys. You use sort keys to not only group and organize data, but also to provide additional means for querying against items in a table.
Sort keys are useful for the following:
They gather related information together in one place where it can be queried efficiently. Careful design of the sort key lets you retrieve commonly needed groups of related items using range queries with operators such as begins_with, between, >, <, and so on.
Composite sort keys let you define hierarchical (one-to-many)
relationships in your data that you can query at any level of the
hierarchy.
Working with sort key, you can use KeyConditionExpression (Working with ranges) to write conditional statements by using comparison operators that evaluate against a key and limit the items returned. In other words, you can use special operators to include, exclude, and match items by their sort key values.
KeyConditionExpression