Recommended Schema for DynamoDB calendar/event like structure

Question

I'm pretty new to DynamoDB design and trying to get the correct schema for my application. In this app different users will enter various attributes about their day. For example "User X, March 1st 12:00-2:00, Tired". There could be multiple entries for a given time, or overlapping times (e.g. tired from 12-2 and eating lunch from 12-1).

I'll need to query based on user and time ranges. Common queries:

• Give me all the "actions" for user X between time t1 and t2
• Give me all the start times for action Z for user X

My initial thought was that the partition key would be userid and range key for the start time, but that wont work because of duplicate start times right?

A second thought:

• UserID - Partition Key
• StartTime - RangeKey

• Action - JSON document of all actions for that start time

  [{ action: "Lunch", endTime:"1pm"},{action:tired, endTime:"2pm"}]

Any recommendation on a proper schema?

Answer 1

This doesn't really have a one solution. And you will need to evaluate multiple options depending on your use case how much data you have/how often would you query and by which fields etc.

But one good solution is to partition your schema like this.

• Generated UUID as partition key
• UserID
• Start time (in unix epoch time or ISO8601 time format)

Advantages

• Can handle multiple time zones
• Can easily query for userID and start date (you will need secondary index with primary key userID and sort key start time)
• More even distribution and less hot keys of your data across dynamoDB partitions because of randomly generated primary key.

Disadvantages

• More data for every item (because of UUID) (+16 bytes)
• Additional cost for new secondary index, note scanning the data in table is generally much more expensive than having secondary index.

This is pretty close to your initial thought, in order to get a bit more precise answer we will need a lot more information about how many writes and reads are you planning, and what kind of queries you will need.