Pages

A database scheme for capturing sensor data

I’ve been monitoring a number of sensors in the house for a while now and storing the results in a simple database table. However the current structure isn’t very flexible. I have one table for electricity data, another for inside/outside temperature, humidity and light levels. Adding more sensors would mean adding more columns or new tables. What I needed is a schema that would allow me to grow the number of sensors without having to change the schema each time more were added.

What I have come up with is a number of tables linked by foreign keys to keep the integrity together.

Monitoring db schema

Tables:

  1. Locations – A list of physical sensor locations, usually a room or location within the house/garden/garage.
  2. Sensors – A list of sensors, measuring a single value. Could be temperature, humidity, light, electricity usage etc. These are linked back to the location table so multiple sensors are present in a location.
  3. Readings – These are the individual time stamped readings from the various sensors.
  4. Pachube_feeds – This is a table to indicate which sensor data maps to a particular pachube feed and datastream. Not all sensors would need or have a pachube feed, but probably most of them. The table could be extended to include the full pachube config details so that new streams can be added to pachube automatically.

The database design was created using MySQL workbench 5.2 beta. This allows you to design your database structure then create the sql that can be used to build the tables. The MySQL workbench 5.2 is a replacement for the old gui tools and includes database design, administration and development all in one tool. The exported sql was executed using phpMyAdmin although it could easily have been done with the command line.

The applications that read the various sensors just need to write the readings to a single table as they are received. Queries can then be performed with joins to retrieve the units, sensor name and location.
If Pachube updates are required then the various feeds and streams can be updated with the latest value for each sensor.

At present one of the jeenodes is sending readings to an arduino receiver that is connected to a linux server running a simple perl script that writes the values to the readings table using a simple insert statement:


INSERT INTO `homeserver`.`readings` (`sensor_id`, `timestamp`, `value`) VALUES ( 1, CURRENT_TIMESTAMP, '12.34' );

The sensor number in this case is 1 and the value is 12.34.

A simple query that gives a summary of readings by location, type, reading and units:

SELECT r.timestamp, l.name as location, s.name as type, r.value, s.units
FROM `readings` r
JOIN `sensors` s ON s.sensor_id=r.sensor_id
JOIN `locations` l ON l.location_id=s.location_id
ORDER BY r.timestamp ASC;

Bookmark this post: bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark

Comments are closed.