Chapter 4 ■ Data transformation
like Listing 4-3. Don’t worry about knowing what all the parts of the command are; just focus on the
readability of the example.
Briefly, the table is the database construct we use to store the data. It forms the layout of the data. We
will learn more about creating tables in the next chapter. For now, notice the columns. Here we see there
are two columns that are named to correspond with the data we are collecting. Indeed, if we were to enter
the data from the previous example, it would look like the results at the end of the listing. I use a SELECT
statement (the method for retrieving rows from the database) to show sample entries.
Listing 4-3. Sample Database Table and Sample Results
CREATE TABLE simple_annotation_test (
id int(11) NOT NULL AUTO_INCREMENT,
temperature float NOT NULL,
barometric float NOT NULL,
notes char(128) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
mysql> SELECT * FROM simple_annotation_test;
+----+-------------+------------+-------+
| id | temperature | barometric | notes |
+----+-------------+------------+-------+
| 1 | 90.82 | 15 | NULL |
| 2 | 91.43 | 15.09 | NULL |
| 3 | 91.13 | 15.23 | NULL |
+----+-------------+------------+-------+
3 rows in set (0.01 sec)
Notice there is also a column for notes. This shows that we can also handle saving any additional notes
or user input along with the samples taken. To do so, we include it when we save data.
DeSCrIptIVe COLUMN NaMeS
this is one of my pet peeves about poor database design habits. that is, you should always use
descriptive names when naming objects (tables), columns, indexes, and so on. that doesn’t mean you
need to use 200 characters for each column name (that would be absurd), but you should avoid the
temptation of using single character names such as a, b, c, and so on.As you can see, annotation of data in the database is accomplished through either naming the columns,
creating columns to store text, or even creating special data types to help annotate the data. For example,
you can define a column to contain a set of values—called enumerations—such that we can specify the
numeric value of the enumeration when we save data. In fact, there are many ways we can do annotation in
the database. We will see more examples in later sections.
Recording the Date and Time
Aside from adding text to the data in the form of column names, notes, and so on, we often need to store the
date and time when an event or series of events are observed. That is, we want to save when the sensor was
read. However, most microcontroller boards and even some low-cost computer boards do not have a real-
time clock (RTC) circuit. For example, the Arduino does not have an RTC nor does a Raspberry Pi.