1

I am setting up DynamoDB. Our data is simple, it has four fields:

  1. UserName
  2. UserShiftEndDate
  3. UserProjectCode
  4. 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

NewBee
  • 33
  • 3
  • I worry this is a little difficult to map out without more info on the access patterns you need for your application. Will your app ask the data store “What project codes does UserB have?” or other sorts of queries? The data shown above seems to suggest your application has several data entities: Users, Projects, Activities, and Work Shifts. Am I reading that correctly? – Peter Wagener Dec 20 '20 at 02:10
  • Yes, you are reading it correct. The only query I need as of now is get all the users data with UserShiftEndDate in future. Input for the query would be date and out put should be all the rows with UserShiftEndDate > Today. I hope this clarifies your question. Thank you ! – NewBee Dec 21 '20 at 05:04

1 Answers1

0

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

Abdul Moeez
  • 1,331
  • 2
  • 13
  • 31
  • Thanks for your response. If I choose ID(Auto generated) as partition key and UserShiftEndDate as sort key , How can I query the users just with UserShiftEndDate ? – NewBee Dec 17 '20 at 03:55
  • As i mentioned in answer that KeyConditionExpression (Working with ranges) to write conditional statements by using comparison operators. – Abdul Moeez Dec 17 '20 at 04:07
  • I updated my answer, u can get a lot of help through official AWS-Doc through that link. – Abdul Moeez Dec 17 '20 at 04:10