Home sensors database - optimising model

Question

I am trying to develop a home automation/monitoring system. This involves a bunch of sensors distributed around the house/garden/garage. I'd like to store the values for the sensor readings. I am trying to make the database design flexible and general so that I don't have to change it around once it is set up. However, I seem to have ended up with two possible options and I can't quite see which is the most optimal. The tables that are common to both are:

tbl_region = id, region_name - (e.g 1, first)
tbl_room = id, region_id, room_name - (e.g 1, 1, lounge)
tbl_sensor = id, sensor_name - e.g (1, dht11)
tbl_measurement = id, measurement_name - e.g (1, temperature)

Since each sensor can have many measurement types (e.g temp, humidty, pressure) and a measurement can be done by many sensors (e.g dht11, bme280) there needs to be a junction table to define the many to many relationship

tbl_sensor_measurement = id, sensortype_id, measurementtype_id - (e.g 1, 1, 1) for a dht11 sensor's temperature measurement (it does other things too)

I can potentially have multiples of the same sensor in the same room so there is a many to many relationship between rooms and sensors. Here is where my two ideas diverge:

1. I could have a junction table between tbl_room and tbl_sensor_measurement:

tbl_room_sensor_measurement = id, room_id, sensor_measurement_id

this would give me mutliple rows for a sensor in a room (one for each type of measurement) and allow the same type of sensor to be housed in that room. This would mean that my final data table would only have four columns

tbl_value = id, room_sensor_measurement_id, value, timestamp

2. I could have a junction table between tbl_room and tbl_sensor

tbl_room_sensor = id, room_id, sensor_id

this would give me one row for a sensor in a room and allow the same type of sensor to be housed in that room. This would mean that my final data table would now have five columns

tbl_value = id, room_sensor_id, measurement_id, value, timestamp

I think that the model 2 is going to be easier to retrieve data later on (I can pick a sensor in a room and get all of the measurements easily. I am new to rdb design and i'm not sure which to go for (or if there is an entirely different way altogether) and would appreciate any pointers.

Thank you for your help,

Martyn

Answer 1

Quick Note

You probably want to ensure that a measurement of 760 mm Mg is not accidentally identified as a Temperature measurement. As such, you really want to keep the two values in different columns (with appropriate CHECK constraints).

Now What?

• Temperature IS A measurement
• Humidity IS A measurement
• Pressure IS A measurement

There are a few ways to build "IS A" relationships in a database.

The method I am recommending has a one "master" measurement table and a "specific" table for each type of measurement. You'll need to decide if you can group some measurements together; in which case, they'll go into the same "specifics" table.

-- pseudo code.  Actual would be RDBMS specific

create table measurements_meta (
  measurement_id  int primary key, -- "Specifics" tables get the same ID.  therefore, the same column name.
  sensor_id       int not null references sensors(id), -- I dropped the unnecessary TBL_ prefix
  room_id         int not null references rooms(id),
  time_of_measure datetime not null default now()
);

create table measurements_temp (
  -- this enforces the 1:0 or 1 relationship
  measurement_id int primary key references measurements_meta( measurement_id ),
  temperature    number not null check ( temperature between -40 and 150), -- F
);

create view temperature
as
select a.*, b.temperature
from measurements_meta a
  join measurements_temp b on a.measurement_id=b.measurement_id;

-- RDBMS Specific
create trigger temperature_tr
instead of insert or update or delete
on temperature
before each row
-- fill in code here to do the actual DML

grant select, insert, update, delete on temperature to my_app_user_role;

In this design, the data Model remains hidden from the application. The application will View the data through the VIEW. Control of the data (DML) is done through simple CRUD operations on the VIEW or through APIs (stored PROCEDURES). TL;DR - I suggest you use the MVC design pattern.

If you can take multiple (simultaneous) readings (eg 2 thermometers for 1 device), the individual readings would go into a child table under that "specifics" table. The "specifics" table would record the average. You would need to use an API (instead of CRUD on the VIEW) which can take in the required number of reading in one call (eg an array of temperatures).