IIoT data

Try me

Problem definition

Your company is involved in a digital transformation project and you would like to design a database to store readings from sensors installed in Industrial Internet of Things (IIoT) devices. For now, your organisation just wants to design a database that it is able to manage data from different types of devices and sensors. There are different device models, so, you want to store the device type information (e.g name and description). Also, there are different types of sensors, so you want to store the information about the sensor type (name, measurement unit, upper range and lower range), for instance, a temperature sensor which measurement unit is celsius degrees and can measure temperatures from -20 degrees to 60 degrees. You will have a network of IIoT devices, and you would like to know what is the location of each device, and the current status (either OK or ERROR). Also, each IIoT device will be connected to a set of sensors, each sensor will be of a specific type, connected to a specific device and will have a status (either OK or ERROR). Finally, you want to store sensor readings, knowing which sensor generated the reading, the timestamp where the measurement reading was created and its value. (for instance, a temperature sensor registered a temperature of 27.5 degrees on the 2021/12/10 at 17:12:00). Design a database model to support this use case.

Database Design

The following diagram shows a possible solution for the database:

The key is to define two tables for the device type and the sensor type where we can store all generic information. This way, the database will scale nicely even if we install many devices equipped with many sensors. Thus, each device will have a type ([1:N] relationship between device_types and devices), and each sensor will have a type ([1:N] relationship between sensor_types and sensors). Finally, each sensor reading will be related to a sensor ([1:N] relationship between sensors and sensor_readings).

Try it yourself

The file IIot MySQL test database contains an implementation of this model, plus some sample data useful to build queries on timestamped data.

Data analysis queries

Try to build the following SELECT queries to analyse the data.

1. Write a SELECTquery to know what is the average value of every sensor

2. Write a SELECT query to know the date of the most recent sensor reading for every sensor

3. Write a SELECT query to show the different sensors of device with ID 1

4. Write a SELECT query to show the number of measurements, average value, maximum value and minimum value of the different sensors of device with ID 1

Data analysis queries solved

1. Write a SELECTquery to know what is the average value of every sensor

The following query uses a GROUP clause to group the reading values by sensor:

SELECT sensor_fk, AVG(reading_value)
    FROM sensor_readings
    GROUP BY sensor_fk;

2. Write a SELECT query to know the date of the most recent sensor reading for every sensor

The following query uses the MAX() aggregated function to find out the date of the most recent sensor reading (since the latest reading of every sensor will have the greatest date)

SELECT sensor_fk, MAX(created)
    FROM sensor_readings
    GROUP BY sensor_fk;

3. Write a SELECT query to show the different sensors of device with ID 1

We could just use a WHERE clause to select just the sensors with device_fk equal to 1. The following alternative solution uses a join clause so that we could add more information of the device if needed.

SELECT d.device_id, s.sensor_id
    FROM devices d
    LEFT JOIN sensors s ON (s.device_fk = d.device_id)
    WHERE device_id = 1;

4. Write a SELECT query to show the number of measurements, average value, maximum value and minimum value of the different sensors of device with ID 1

We extend on solutions of previous queries to build the following query where the measurements are grouped by sensor_id

SELECT d.device_id,
        s.sensor_id,
        COUNT(reading_value) AS total_measurements,
        AVG(reading_value) AS average,
        MAX(reading_value) AS maximum,
        MIN(reading_value) AS minimum
    FROM devices d
    LEFT JOIN sensors s ON (s.device_fk = d.device_id)
    LEFT JOIN sensor_readings sr ON (sr.sensor_fk = s.sensor_id)
    WHERE device_id = 1
    GROUP BY sensor_id;