How to design DynamoDB table to facilitate searching by time ranges, and deleting by unique ID

1
votes

I'm new to DynamoDB - I already have an application where the data gets inserted, but I'm getting stuck on extracting the data.

Requirement:

  1. There must be a unique table per customer
  2. Insert documents into the table (each doc has a unique ID and a timestamp)
  3. Get X number of documents based on timestamp (ordered ascending)
  4. Delete individual documents based on unique ID

So far I have created a table with composite key (S:id, N:timestamp). However when I come to query it, I realise that since my id is unique, because I can't do a wildcard search on ID I won't be able to extract a range of items...

So, how should I design my table to satisfy this scenario?

Edit: Here's what I'm thinking:

Primary index will be composite: (s:customer_id, n:timestamp) where customer ID will be the same within a table. This will enable me to extact data based on time range.

Secondary index will be hash (s: unique_doc_id) whereby I will be able to delete items using this index.

Does this sound like the correct solution? Thank you in advance.

1
amazon-dynamodb

1 Answers

1
votes

You can satisfy the requirements like this:

Your primary key will be h:customer_id and r:unique_id. This makes sure all the elements in the table have different keys.

You will also have an attribute for timestamp and will have a Local Secondary Index on it.

You will use the LSI to do requirement 3 and batchWrite API call to do batch delete for requirement 4.

This solution doesn't require (1) - all the customers can stay in the same table (Heads up - There is a limit-before-contact-us of 256 tables per account)