0

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
  • 2
    Hey NewBee, can you please give us more information what you want to query, maybe with an example? Do you want to find a single user or all users that have shift end at the same time? [Stackoverflow now has table support](https://meta.stackexchange.com/questions/356997/new-feature-table-support?cb=1), which should help you create a "demo" table here. – Jens Dec 15 '20 at 02:31
  • @Jens , Thanks for your response, I edited my question. I hope the question is more clear now. – NewBee Dec 15 '20 at 03:11

1 Answers1

0

I would recommend creating a UUID (programmatically generated) as a primary key and then a GSI with the UserShiftEndDate. So that you can provide a date and get all the records that has the provided date. Please see this article for more information.

CruncherBigData
  • 1,112
  • 3
  • 14
  • 34
  • Thanks for your response, but I think GSI with UserShiftEndDate is not feasible here as I want to get the rows with UserShiftEndDate > Today. But I can't do this with querying with partitional key as UserShiftEndDate . As in the query I can't apply > filter on partition key – NewBee Dec 21 '20 at 05:00