DynamoDB table design pattern: catagorized data with utc time stamp

0
votes

I'm currently designing a DynamoDB pattern I don't know if I'm on the right track or not.

I have the following data I need to store into a DynamoDB table:

  • deviceID (number)
  • deviceLogType (string)
  • timestamp (number) // timestsamp is in utc seconds
  • other misc attributes

The primary query for this DynamoDB table is to query the data by both deviceID and deviceLogType over a time range. Is

Partition Key : deviceLogType_deviceID

Sort Key : timestamp

My questions are:

  • Is the above design correct?
  • Is it within the best practice to use composite key for partition key to represent hierarchical relations?
2
amazon-web-servicesamazon-dynamodbamazon-dynamodb-index
Do you ever need to search for "all deviceLogTypes for a given deviceID and time range" or "all deviceIDs for a given deviceLogType and time range"? If so, how many deviceLogTypes are there, and is that number ever going to grow? – Matthew Pope
which is the hierarchical relation more exactly? – Horatiu Jeflea
@MatthewPope, No, I don't need to search for either. There's a fix number of deviceLogTypes. – ADF11F
@HoratiuJeflea, the hierarchical relation: deviceLogType -> deviceID – ADF11F

2 Answers

0
votes
deviceLogType_deviceID (hash key)
timestamp (sort key)
deviceID (I would keep this separate for future query possibilities)
deviceLogType (I would keep this separate for future query possibilities)
other misc attributes

Using this schema, you can use your current query and will open future possibilities for querying only by deviceId or deviceLogType (only add an index).

Composite key is indeed a good practice (best to have a separator which will not appear in both values), but not sure about which inheritance you mean.

But for example if we have AndroidDeviceLogType > DeviceLogType, best practise would be to store that value in deviceLogType in a serialised form (JSON in Base64 for example, or just plain JSON...).

0
votes

Your approach will work for the use case you have described.

It is acceptable to use a composite partition key. You cannot perform any hierarchical queries using partial matching on a partition key, but historically it had the added benefit of helping to prevent hot partitions. Hot partitions are now a thing of the past, so you don't need to be concerned about that anymore.

There is only one possible problem I see. If you have more than one log entry with the same deviceLogType_deviceID and timestamp, the last one will overwrite all previous entries with the same timestamp. To avoid that, you can store the timestamp as an ISO 8601 string and append a UUID to the end of the timestamp to prevent primary key conflicts.