# Advice on Database Design for Home Automation

**URL:** <https://discourse.nodered.org/t/advice-on-database-design-for-home-automation/29078>\
**Category:** General\
**Created:** [27 June 2020 15:48 UTC](https://discourse.nodered.org/t/advice-on-database-design-for-home-automation/29078 "2020-06-27T15:48:16Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![martynwheeler](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/martynwheeler/32/24731_2.png) [@martynwheeler](https://discourse.nodered.org/u/martynwheeler)\
**Post date:** [27 June 2020 15:48 UTC](https://discourse.nodered.org/t/advice-on-database-design-for-home-automation/29078/1 "2020-06-27T15:48:17Z")

</div>

Hi,

I have just discovered node red and it seems fantastic. I am trying to put together a smart home system and it seems really quick to do development. At the moment I am just in the process of setting up various sensors (dht22, bme280, ds18b20, etc) around the house (the smart bit will come later). I want to store the values to a db (I have been just using python up until now to insert into sqlite tables for each sensor). I am now ready to make the database a bit more coherent and I was wondering if anyone could give me some feedback on the design before I put it into action (I know it is not exactly a node red question but I think that lots of people here are probably doing similar stuff). Anyway here is the structure that I am proposing. It is basically a relational database to store all values in one table with references to regions and sensors in the others. I am pretty new to db design so would appreciated any feedback before going too much further.

Thanks in advance for reading,

Martyn

DB design:

-- Table: tbl\_measurement  
CREATE TABLE tbl\_measurement (id INTEGER PRIMARY KEY AUTOINCREMENT, measurement VARCHAR (15) NOT NULL);

-- Table: tbl\_region  
CREATE TABLE tbl\_region (id INTEGER PRIMARY KEY AUTOINCREMENT, region VARCHAR (20) NOT NULL);

-- Table: tbl\_room  
CREATE TABLE tbl\_room (id INTEGER PRIMARY KEY AUTOINCREMENT, region\_id INTEGER CONSTRAINT fk\_region REFERENCES tbl\_region (id) NOT NULL, room VARCHAR (20) NOT NULL);

-- Table: tbl\_room\_sensor\_measurement  
CREATE TABLE tbl\_room\_sensor\_measurement (id INTEGER PRIMARY KEY AUTOINCREMENT, room\_id INTEGER CONSTRAINT fk\_room REFERENCES tbl\_room (id) NOT NULL, sensor\_measurement\_id INTEGER NOT NULL CONSTRAINT fk\_sensor\_measurement REFERENCES tbl\_sensor\_measurement (id), instance INTEGER NOT NULL, CONSTRAINT un\_room\_sensor\_instance UNIQUE (room\_id, sensor\_measurement\_id, instance) ON CONFLICT ROLLBACK);

-- Table: tbl\_sensor  
CREATE TABLE tbl\_sensor (id INTEGER PRIMARY KEY AUTOINCREMENT, sensor VARCHAR (15) NOT NULL);

-- Table: tbl\_sensor\_measurement  
CREATE TABLE tbl\_sensor\_measurement (id PRIMARY KEY, sensor\_id INTEGER CONSTRAINT fk\_sensor REFERENCES tbl\_sensor (id) NOT NULL, measurement\_id INTEGER CONSTRAINT fk\_measurement REFERENCES tbl\_measurement (id) NOT NULL, CONSTRAINT un\_sensor\_measurement UNIQUE (sensor\_id, measurement\_id) ON CONFLICT ROLLBACK);

-- Table: tbl\_value  
CREATE TABLE tbl\_value (id INTEGER PRIMARY KEY AUTOINCREMENT, room\_sensor\_measurement\_id INTEGER CONSTRAINT fk\_room\_sensor\_measurement REFERENCES tbl\_room\_sensor\_measurement (id) NOT NULL, value NUMERIC NOT NULL, timestamp INTEGER (4) NOT NULL);

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [27 June 2020 16:30 UTC](https://discourse.nodered.org/t/advice-on-database-design-for-home-automation/29078/2 "2020-06-27T16:30:14Z")

</div>

Before you go too far I suggest having a look at influxdb which is specifically designed for time series databases and (in conjunction with Grafana for sophisticated charts etc) is very commonly used for just the sort of system you are describing.  
It has built in features for things like automatically downscaling old data, so maybe keep all data for a year, but for older ones keep just, for example, the max and min values for each day, or perhaps just once an hour, or whatever takes takes your fancy.  
There is a bit of a learning curve though, it isn't quite the same as the traditional database.

---

<div class="post-metadata">

**Author:** ![martynwheeler](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/martynwheeler/32/24731_2.png) [@martynwheeler](https://discourse.nodered.org/u/martynwheeler)\
**Post date:** [27 June 2020 16:36 UTC](https://discourse.nodered.org/t/advice-on-database-design-for-home-automation/29078/3 "2020-06-27T16:36:18Z")

</div>

@Colin, thanks for the heads up. I will look into it.

---

<div class="post-metadata">

**Author:** ![martynwheeler](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/martynwheeler/32/24731_2.png) [@martynwheeler](https://discourse.nodered.org/u/martynwheeler)\
**Post date:** [28 June 2020 09:23 UTC](https://discourse.nodered.org/t/advice-on-database-design-for-home-automation/29078/4 "2020-06-28T09:23:30Z")

</div>

@Colin - having done some reading I agree and influxdb is the way to go. It is a very different way of viewing things but seems to make a lot of sense. Before I go too far can I ask you a follow up question please?  
is it better to split things into different measurements (temperature, humidity, pressure, etc) or group them by sensor like in this example ([https://www.definit.co.uk/2018/07/monitoring-temperature-and-humidity-with-a-raspberry-pi-3-dht22-sensor-influxdb-and-grafana/](https://www.definit.co.uk/2018/07/monitoring-temperature-and-humidity-with-a-raspberry-pi-3-dht22-sensor-influxdb-and-grafana/))

If I went with breaking it down to measurement type it would give be one database for all rooms/areas and I could use the MQTT message to build tags.

e.g. home/outside/garage/bme280/1 would give me the tags  
(region=outside,room=garage,sensor=bme280,instance=1)

which would give me three points

temperature,region=outside,room=garage,sensor=bme280,instance=1 value=27.0 1434067467000000000  
humidity,region=outside,room=garage,sensor=bme280,instance=1 value=55.0 1434067467000000000  
pressure,region=outside,room=garage,sensor=bme280,instance=1 value=1010.0 1434067467000000000

I can then query those by the tags later on.

Does this seem as sensible approach?

Thanks for pointing me towards influxdb,  
Martyn

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [28 June 2020 09:56 UTC](https://discourse.nodered.org/t/advice-on-database-design-for-home-automation/29078/5 "2020-06-28T09:56:54Z")

</div>

> [@martynwheeler](#):
>
> is it better to split things into different measurements (temperature, humidity, pressure, etc) or group them by sensor like in this example

I tend to group by place and sensor since that is most likely how you will want to view/query the data later. Then each sensor type (temp, humidity, light, etc) are fields holding values. Here is a quick example of a query in Grafana:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/a/9a7848af2ad5ac84348dd56ec2c256fb735189ad.png)

---

<div class="post-metadata">

**Author:** ![martynwheeler](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/martynwheeler/32/24731_2.png) [@martynwheeler](https://discourse.nodered.org/u/martynwheeler)\
**Post date:** [28 June 2020 10:16 UTC](https://discourse.nodered.org/t/advice-on-database-design-for-home-automation/29078/6 "2020-06-28T10:16:07Z")

</div>

Thanks for that. I haven't got into grafana yet, but will take that on board.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [28 June 2020 13:25 UTC](https://discourse.nodered.org/t/advice-on-database-design-for-home-automation/29078/7 "2020-06-28T13:25:24Z")

</div>

I use one measurement for all the Home stuff and another for Weather. Within the Home I use a tag to identify the room then fields for temperature, humidity and so on. I don't identify a sensor by id or type in the database because when looking at historical data, for example, you want to know bedroom temperature and don't care what sensor it came from. If you changed the sensor you wouldn't want to have to select a different field in the database to see it. One advantage of doing it that way is that (again in grafana) you can use auto-repeating dashboards so that the display for each room is identical, the only difference being the room tag.  
I also have a simple mapping from MQTT topic to influx room and sensor so that the influx flow does not need knowledge of particular rooms or sensors, it just looks at the topic and knows what to do with it. So for example the topic for bedroom temperature is home/bedroom/temperature and that goes to tag bedroom, field temperature.  
The sensors for different rooms are in some cases connected to different Pis, but that doesn't matter, the influx flow just has to subscribe to the appropriate topics and the rest is automatic.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [27 August 2020 13:25 UTC](https://discourse.nodered.org/t/advice-on-database-design-for-home-automation/29078/8 "2020-08-27T13:25:25Z")

</div>

Closed on 27 August 2020 13:25 UTC

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.