Chapter 5 ■ MySQL priMerStep 1: Describe the Data
The first thing you should do when designing a database is to describe the data as completely as you can.
You should describe the data in English terms perhaps even writing it out on a piece of paper. Doing so helps
you define what the data looks like conceptually so that you can determine how many objects you want to
store, what they are composed of, and how to organize them.
The plant-monitoring system should store information about plants. Specifically, we want to store
information that tells us when plants need watering and how often. It is also important to know the name
of the plant, where it is located, and whether it is inside or outside. Thus, the data we need to store consists
of the plant name, location, whether they are inside or outside, soil moisture sensor value, temperature
sensor value, and time of the sensor reading. We also determine that we want to quantify the value of the
soil moisture to make it easy to write an application to detect when a plant needs watering. Indeed, we could
even add an automatic watering feature in the future!
Since the plant name is always the same, we don’t need to store that information more than once. Thus,
we will create one table to store the information about the plant we are monitoring and create another table
to store the sensor readings. This way, if you need to change the name of a plant (like we saw previously)
or you want to change its location, you need to change it in only one place. Since we will store the sensor
readings in a different table, we must choose a column to be used to join the tables. Since we don’t have any
reasonable numeric value assigned to each plant, we can use the auto increment feature to add a unique key
(in this case the primary key).
Step 2: Design the Database Objects
Now let’s see how these tables would look like. We name the master table (the one that stores the plant
information) plants and the detail table (sensor readings) readings. We place each of these tables in the
database named plant_monitoring. Listing 5-8 shows the layout or schema of each of the tables in the
database.
Listing 5-8. Plant-Monitoring Schema
-- A database for storing plant soil moisture and ambient temperature
CREATE DATABASE plant_monitoring;
USE plant_monitoring;
-- This table stores information about a plant.
CREATE TABLE plants (
id int(11) NOT NULL AUTO_INCREMENT,
name char(50) DEFAULT NULL,
location char(30) DEFAULT NULL,
climate enum ('inside','outside') DEFAULT 'inside',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- This table stores values read from sensors. The sensors are read
-- periodically by a sensor node and the values stored as a single row
-- with the date and time added by the database server.