I have a DDB table with some attributes. Mainly request_id, item_id, item_status, created_timestamp, user_id etc.
Details about the data:
One request can have multiple item_ids. And initially all the item_ids have the same item_status, RECEIVED. This item_status attribute for an item keeps changing at multiple different times by multiple different lambdas. item_id itself is unique. request_id and item_status are not not unique values.
Initially when the request is made, my lambda creates records in the table with all these details.
Then there is another lambda that fetches the records from the table. Now here is the issue. I want the lambda to fetch all the records from the table that have a particular item_status.
For my table's structure/schema, I chose request_id and item_id as partition and sort keys respectively. But I have many queries based on item_status. Some examples are fetch the item_ids for which item_status is a particular value, fetch the item_ids given the request_id and item_status.
I thought of a GSI with item_status and request_id as partition and sort keys. But this combination is not unique. How do I solve this?
I have many other fields in the table that cannot be used. For example, zip code. I cannot think of a way to make my key unique.
I can fetch based on request_id and filter in the code. But that is too difficult given the size of the table and that this has to be done multiple times in multiple lambdas.
I thought of a GSI with item_status and request_id as partition and sort keys. But this combination is not unique. How do I solve this?
The GSI attribute combination does not need to be unique. Their developer guide says:
In a DynamoDB table, each key value must be unique. However, the key values in a global secondary index do not need to be unique.
Go on with GSIPK: item_status and GSISK: request_id.
You can Query items on a GSI, but you can't GetItem a specific item on it, since it's not required to be unique. You could query on the GSI in a way that you know only a single item will be returned, then simply access the first and only item in the response.