Lost with dynamodb table definition

Question

I'm a little lost with dynamodb table definition and Keyschema. Here's what i want to achieve :

I'm creating a table to store reporting information. This reporting will be in the folliwing format :

itemId, accountId, date, typeOfMetric, metric1, metric2, metric3

At the moment i expect typeOfMetric to be monthlyReport, or dailyData for example. accountId is for users who are grouped into accounts. So each account can access their own data.

Typically i'm thinking to query the table this way :

get all items with accountId=123 and typeOfMetrics=daily
get one item with accountId=123 and typeOfMetrics=daily and date=2021-11-15

And i'm a little lost with the keyschema and the indexes i should create, any help very welcome!

Answer 1

We can choose accountId as PrimaryKey and date as our sortKey. This will help us query over range.

I guess this will cater your requests for typeOfMetrics:Daily. And, if we are looking for monthly, we can query over date over the month.

If this doesn't fits your use-case, let us know.

Answer 2

I ended up doing the following :

AccountId -> PrimaryKey SK -> SortKey

This allows to have items like : accountId REPORTDAY#2021-01-21 stats1, stats2 accountId REPORTMONTH#2021-01, stats1, stats2

And query that with begginWith