MySQL for the Internet of Things

Chapter 5 ■ MySQL priMer

Aha! Now, we’ve got it. Well, almost. We have too much information. But at least we can see the
information is correct. Now, we just want the plant name and location. Let’s limit that output a bit.

mysql> SELECT name, location
-> FROM soil_status_today JOIN plants ON soil_status_today.id = plants.id
-> WHERE (num_events/max_samples_today(plants.id)) > 0.50 AND soil_status = 1;
+------------------+----------+
| name | location |
+------------------+----------+
| Jerusalem Cherry | deck |
+------------------+----------+
1 row in set (0.00 sec)

And there it is! Easy, right? Well, not so much if this was your first attempt, but notice how much easier it
is to write the query when we use tools such as views, functions, and aggregate features!
Now that we have seen a complete and working example, let’s consider the recommendations and best
practices for designing databases for IOT solutions.^15

Recommendations and Best Practices

Let’s review the best practices and recommendations for how you should build you database(s) for your IOT
solution. This section presents a number of tips for designing databases. That said, this section cannot cover
all there is to learn or know about database design. Indeed, there are many more, especially for enterprise-
level database design and management. Thus, it would require several chapters, many pages, and an entire
book many times the size of this section to do it justice. However, I feel it is important to close out the
discussion of learning to use MySQL and databases in your IOT solutions with a reference for you to review
when planning your IOT database design. I list them in no particular order.

• Use the correct data type: Do your homework and determine the best data type to use
  for each column. Avoid the temptation of using all character fields, which can make
  comparisons return false results or frustrate you as you try to figure out why a view or
  function fails.


• Use the smallest data type: Try to use the smallest data type for your data. For
  example, avoid using wide character fields. You can always increase the size if you
  need to (should you start truncating the data). Similarly, use the binary type that
  matches the maximum value you will store. For example, if you do not need high
  precision or big numbers, do not use double or long integer data types.


• Use indexes: For queries on data that is more than a few dozen rows, consider adding
  indexes on the frequently queried columns. Indexes can vastly improve joins and
  complex queries such as ranges if there are indexes on the columns being compared
  or calculated.


• Don’t store redundant data: Try to avoid the temptation of storing the same value
  in multiple tables. It may make your queries easier to write but can make updates
  difficult. That is, how would you know where every occurrence of the column exists
  for larger databases? Use master-detail relationships to simplify the data.

(^15) Actually, adhering to best practices for any database design.