I currently have a maintenance log, tracking jobs performed across various machines (date/running time/new parts etc). Currently, this is backed by a relational database, which automatically increments an id number for each job. I would like to move this database to DynamoDB, but I am unsure about how to structure the database, whilst keeping unique primary keys for each item.
My initial thought would be to have machine as the partition key, as the main query would be "show me all jobs for machine x", but then what do I use for the sort key? I have ruled out date, as a sort key, as there are often multiple jobs on the same machine each day. Could I get away with using the epoch timestamp of the time that the log is added to the database, even though that is not important data? Or maybe generate some kind of unique id?
The PK and SK must form a unique pair. In your case, the SK has to make it unique if the search pattern is only "Get all logs for this machine".
Just put something like a timestamp (if that is unique enough) in as an SK, and then retrieve the data by only using the PK. However as mentioned in the comments, perhaps DynamoDB is not the best place to store logs