A well-structured database is key to efficiently storing and retrieving the vast amount of data collected by your GardenPi system. Let’s delve into the design and schema of the SensorDataDB MySQL database, which plays a critical role in managing your garden data.

Understanding the Schema, Tables, and fields

The SensorDataDB database consists of a schema of several interconnected tables, each with fields (columns) serving a specific purpose:

sensorMeasurements Table: This is the largest table, recording the raw sensor data
- OI Field: Identifies rows in the table as they are inserted
- timestamp Field: The date and time of a given reading
- sensorOI Field: The number identifying the sensor being recorded. This is a critical field for the schema joins
- sensorRead Field: The value of the sensor being read
plantData Table: It stores information about the plants associated with each sensor
- OI Field: Identifies rows in the table as they are inserted
- sensorOI Field: The number identifying the sensor being recorded This is a critical field for the schema joins
- plant Field: The type of plant (e.g. Roma Tomato, Basil, etc.)
- installDate Field: The date the given sensor starts recording in the plant
- removeDate Field: The date the given sensor stops recording in the plant
sensorData Table: This table contains details about each sensor
- sensorOI Field: The number identifying the sensor being recorded This is a critical field for the schema joins
- sensorName Field: The name of the sensor (e.g. Capacitive Moisture Sensor 2.0, BME280, etc.)
- sensorType Field: The type of sense being recorded (e.g. Moisture, Humidity, Temperature, etc.)
- unitType Field: The unit of measurement (e.g. Celsius, Kilo Pascal, etc.)
- unitShort Field: The short version of the unit of measurement (e.g. C, kPa, etc.)
- installDate Field: The date the given sensor is installed in the project
- removeDate Field: The date the given sensor is removed from the project
sensorWatering Table: It logs instances when watering is detected by a python script based on moisture sensor data
- OI Field: Identifies rows in the table as they are inserted
- datetime Field: The date and time of a given reading
- sensorOI Field: The number identifying the sensor being recorded This is a critical field for the schema joins
- preValue Field: The value recorded for a given sensor before the drop detected
- postValue Field: The value recorded for a given sensor after the drop detected
- percDrop Field: The percentage drop recorded
sensorMoved Table: It logs instances when the sensor is moved in a plant as detected by a python script based on moisture sensor data
- OI Field: Identifies rows in the table as they are inserted
- datetime Field: The date and time of a given reading
- sensorOI Field: The number identifying the sensor being recorded This is a critical field for the schema joins
- preValue Field: The value recorded for a given sensor before the drop detected
- postValue Field: The value recorded for a given sensor after the drop detected
- percGain Field: The percentage gain recorded

We plan on recording in 15 minute intervals so the main table will grow by 96 rows per sensor per day which will quickly add up. The tables are structured to minimize the memory required by keeping the main sensorMeasurements table narrow and minimizing the memory required for each row. The other identifying information can be easily joined into a view, sensorReadingsView, to add in more context at far less memory than one giant table with everything.

Visualizing the Relationships

Full Schema including view, not the best but you get the idea

Creating the database, user, and tables

step 1: create the database

With our basic understanding in hand, now we will actually create the database. But first we need to download and install MySQL. Connect to the Raspberry Pi Terminal from your PC via PuTTY and issue the following commands to connect to MySQL:

sudo mysql -u root -p

Enter the Username and Password you created back in Raspberry Pi Setup to login. From here the first thing we want is to create the database. Enter the following command:

CREATE DATABASE sensorDataDB;

This creates the base database where we can store the tables and data.

step 2: create the user and grant access

Next we want to create a user (instead of using ‘root’) to access the data. Enter the following command:

CREATE USER 'exampleuser'@'localhost' IDENTIFIED BY 'password';

The above creates the user ‘exampleuser’ with the password of ‘password’. You should be more thoughtful and think of something in line with your project. Enter the following command to give the user privaleges to access and update the database:

GRANT ALL PRIVILEGES ON SensorDataDB.* TO 'exampleuser'@'localhost';

FLUSH PRIVILEGES;

Note: Remember this step as you will use it again when creating a user with your PC IP address for remote querying of the database

step 3: create tables and Views

With the database and user created, now we can create the tables to store the data. Enter the following commands sequentially:

CREATE TABLE sensorMeasurements (
  OI integer NOT NULL AUTO_INCREMENT,
  timestamp datetime, 
  sensorOI integer,
  sensorRead double, 
  PRIMARY KEY (OI) 
);

CREATE TABLE plantData (
  OI integer NOT NULL AUTO_INCREMENT, 
  sensorOI integer,
  plant varchar(20),
  installDate datetime,  
  removeDate datetime,   
  PRIMARY KEY (OI)
);

CREATE TABLE sensorData (
  sensorOI integer NOT NULL AUTO_INCREMENT,
  sensorName varchar(50),
  sensorType varchar(50), 
  unitType varchar(50),
  unitShort varchar(10),
  installDate datetime,
  removeDate datetime,
  PRIMARY KEY (sensorOI)
);

CREATE TABLE sensorWatering (
  OI integer NOT NULL AUTO_INCREMENT 
  ,datetime datetime,  
  sensorOI integer,
  preValue double,
  postValue double,
  percDrop double,
  PRIMARY KEY (OI)
);

CREATE OR REPLACE  VIEW sensorReadingsView AS
SELECT
    sm.timestamp,
    CASE
        WHEN p.plant IS NOT NULL THEN CONCAT(sd.sensorOI, ' ', sd.sensorType, ' ', p.plant)
        ELSE CONCAT(sd.sensorOI, ' ', sd.sensorType)
    END AS sensor,
    CASE 
        WHEN p.plant IS NOT NULL THEN 
            (sm.sensorRead - (SELECT MIN(sensorRead) FROM sensorMeasurements)) / 
            ((SELECT MAX(sensorRead) FROM sensorMeasurements) - (SELECT MIN(sensorRead) FROM sensorMeasurements)) * 100
        WHEN sm.sensorOI = 6 THEN (sm.sensorRead/100)  
        ELSE sm.sensorRead 
    END AS sensorRead
FROM sensorMeasurements sm
INNER JOIN sensorData sd ON sm.sensorOI = sd.sensorOI
LEFT JOIN plantData p ON sm.sensorOI = p.sensorOI;

With the tables and views created, all that is left to do is create the scripts to populate the tables and set them on cron jobs.

Database Schema and Setup: The Backbone of GardenPi