Chapter 4 ■ Data transformation
Notice here we have three tables. The base table or the table with the core information is named plants.
We see a separate table to store soil moisture data and another to store temperature. The tables share a
common column, id, which is used to uniquely identify the plant for which the sensor data was read. That
is, we can retrieve all the temperature or soil moisture values for a given plant (the column id is a foreign key
for the temperature and soil_moisture tables). Notice we also store a timestamp for each row so we know
when the sensor data was stored and therefore can plot data over time or determine rates of change.
■Note the drawing in figure 4-2 was taken from the visual database editor in mysQL Workbench
(http://dev.mysql.com/downloads/workbench/).
By making three tables instead of one we have preserved the potential to gain knowledge from the data,
which is being read at different intervals. Indeed, this example shows how powerful the database can be.
Sure, you can implement this solution in a file-based solution, but the database makes it far easier to see and
even easier to set up.
Interval Driven Data
There are solutions where it is more important to store data on a fixed or ad hoc interval. In these solutions,
the focus is on gathering the data for use at a specific time. Thus, it is likely the solution will not store data
unless or until the data is requested. Some solutions make the data available dynamically but generally do
not store it until the user or some other mechanism initiates the request.
For example, consider a weather station. If you were building a weather station yourself, you would
probably incorporate a display element to show the current values of weather observations such as
temperature, barometric pressure, and so on, in real time. In this case, the data from the sensors is read
frequently, and the display element (for example, gauge) is updated frequently. However, the data for these
values is not likely to be stored.
Rather, for a weather station, the data saved is typically a set of observations at a specific time period.
You may see weather stations that store data once per hour or even several times per hour. This is because
the observations have a common data element—time of collection. Thus, interval driven data may require
you to build your solution to store a host of data at specific intervals and not at the interval of a specific
sensor or event.
For these solutions, we typically store the information as a single entry (row). However, there is nothing
that says we have to do so. We still may want to store the data in separate tables (files) if there is a need to do
so, but there isn’t normally an issue related to the data or knowledge gained. Again, this is because the data
is saved as specific intervals across the sensors rather than being driven by when sensor data is available.
Figure 4-2. Collecting data from multiple sensors at different rates