Call-for-Code / Call-for-Code/Prometeo-Pyrrha

Define schema for sensor data table

Open
#112 10 comments 1 reaction 4 assignees Claimed by @JSegrave-IBM View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.