Call-for-Code / Call-for-Code/Prometeo-Pyrrha
Define schema for sensor data table
- Dominant language
- JavaScript
- Stars
- 27
- Forks
- 18
- PR merge metrics
- No merged PRs in 30d
Description
Proposed Schema below.
**Key points:**
* There are two tables:
* `firefighter_sensor_log` where each row is the raw/unmodified sensor readings associated with `firefighter_id` during the minute `timestamp_mins` - we never modify this data
* `firefighter_status_analytics` - where each row the same data as the sensor log, plus all the analytic information about `firefighter_id` during the minute `timestamp_mins`. This is a kind of one-stop-shop for data analysis - everything about the firefighter in one place. Making it a separate table to `firefighter_sensor_log` allows us to keep an untouched record of what was sent. It also removes a few potential contention points between the processes that read and write information.
* Keys for both tables are (`timestamp_mins`, `firefighter_id`) - note the ID change from previous `SensorID`
* `timestamp_mins` is a minute-quantized timestamp. Important that these keys be minute-quantized - e.g. '10/02/2020 09:40' not '10/02/2020 09:40:34'. Also note the name change - because plain 'ol *timestamp* is an SQL keyword
* `device_timestamp` is the unmodified original device timestamp.
* The sensors and all time-weighted averages (TWAs) are floats because the ranges vary a lot (from 10^3 to 10^-3)
* The gauges are all ints because they're the percentage over/under the limit - we don't need float-level detail.
* Example dataset from Feb (with real CO and mocked-up NO2, but no other gases) is on [box here](https://ibm.ent.box.com/folder/115709497727).
* Using the gas name, because while 'CO' and 'NO2' have become familiar, CH2O, C3H4O and C6H3(CH3)3 ... not so much :-) not a strong opinion though (it's a database, only technical people will see it!), so feel free to change this.
* Normally the `device_status_LED` and `analytics_status_LED` (i.e. the LED colors) will be the same, but in a disconnected scenario, they may be different. We're capturing both. Associated values are { 1 = green ; 2 = yellow ; 3 = red } and for this release, 'yellow' is a configurable percentage, currently defaulting to 80% (i.e. any gas reaching 80% of any AEGL-2 time-period limit will cause the LED to go yellow)
* DEFAULT CHARSET is set to `UTF8mb4;` as per discussion with Dan about internationalization.
See Table definition for details below
Contributor guide
Assessment
This issue has not been assessed yet.