I want to design a schema for AWS Timestream for the following:
- A single table across any number of sites (clients)
- Each site has many (dozens) of sensors - values are arrays of data. Number of sensors varies by site.
- Each site has many (dozens but fewer than sensors) relays - values are ON (1) or OFF (0) and represent the state of the relay. Number of relays varies by site.
- Each site reports in every minute with all sensor readings and all relay states in a single request
Is it sane in this scenario to save sensor reading data and relay states in different tables and store a single record per sensor or relay (i.e. many rows generated in multiple tables per minute)? Or is there a compact and versatile alternative allowing:
- The number of sensors/relays to vary by client (and to change as a client grows/shrinks over time)
- Efficient and cost effective querying
I tried multiple MULTI fields ('sensors' and 'relays') but I believe I come unstuck because each sensor reading is itself an array. i.e. 'sensors' is an array of arrays or as Timestream describes it, 'sensors' contains nested measures and this is not yet supported.
Any guidance much appreciated.