I'm new to DynamoDB and NoSQL, so I'm trying to get my head wrapped around table design.
I have a simple app that will accept status reports about a series of locations. Each status report will have a location ID (number), the status (number), and a timestamp (string). I also need to store a description of each location, which will have a location ID (number) and four other values (all strings).
The read pattern is to query the most recent status report for a subset of location IDs (that may change each time). I can use the design pattern where you query with a limit of 1 and set Scan Forward to false for each location ID. The report that will be built needs to include the status, the timestamp, and the description for each of the queried locations.
The first part of the design is easy -- the partition key is the location ID, and the sort key is the timestamp. Next, though -- how do I store the description data?
(Important: the app is supposed to be run via several Lambda functions, so I'm a bit limited in terms of caching the description info. Eventually, the number of locations will be in the thousands.)
Option 1: Store the description info in each status report. I will need to do two transactions each time a status report comes in -- read the description info, then write the new status report item including the four description fields. Reads then become easy, as the info is all there.
Option 2: Store the store the descriptions as separate items in the same table. Use a Global Secondary Index to query the description info. When a report is run, do two reads for each location, one to get the status and timestamp, one to get the description. Receiving a status report needs only one transaction, a write. If this is the best option, how should I structure the GSI?
Option 3: Store the descriptions in a separate table. Everything else is as per Option 2.
Any and all advice greatly appreciated.
