Chapter 5 ■ MySQL priMer- Normalize your database: Normalization is a big deal for a lot of database experts
and rightly so, but for the enthusiast and hobbyist getting your database into one
of the higher normal forms (from relational database theory) may be far too much
work. That said, I encourage you to strive for third normal form,^16 but don’t kill
yourself getting there. You can ruin a large database quickly trying to over normalize.
A small amount of under normalization is permissible if efficiency is not lost to gain
simplicity. - Design your database before coding: Always design your database after you’ve
defined the high-level requirements but before you implement the source code. This
may sound backward, but it is an excellent habit to form. - Test, test, test! I cannot stress this enough. Time spent testing your database and more
importantly your queries with known (test) data will save you a lot of headaches later
when you start developing an application to query and present the data. - Back up your data: Once you have your solution running, perform a backup on the
data. If you do not have a lot of data, tools such as mysqldump, MySQL Utilities,^17 and
similar can make a logical backup of your data in SQL form, which you can restore
should you need to do so. If your data is larger, say gigabytes or more, you should
consider a commercial backup solution such as MySQL Enterprise Backup. - Document your database: It may seem like extra work if your database contains only
a single or small number of tables and little data, but imagine what would happen if
your solution ran for years without issue and then one day you needed to add new
features or troubleshoot a problem. If you don’t know what the database stores or
how it produces the results (views, triggers, and so on), you may spend a lot of time
digging into false leads. You can document your database in a number of ways. I like
to store the SQL statements in a file and write short descriptions of each object. See
the example code for this chapter for an example.
Summary
The MySQL database server is a powerful tool. Given its unique placement in the market as the database
server for the Internet, it is not surprising that IOT developers (as well as many startup and similar Internet
properties) have chosen MySQL for their IOT solutions. Not only is the server robust and easy to use, it is
also available as a free community license that you can use to keep your initial investment within budget.
In this chapter, you discovered some of the power of using a database server, how database servers store
data, and how to issue commands for creating databases and tables for storing data as well as commands
for retrieving that data. While this chapter presents only a small primer on MySQL, you learned how to get
started with your own IOT data through the example IOT solution shown. It is likely your own IOT solution
will be similar in scope (but perhaps not the same database objects or table layout [schema]).
In the next chapter, you will see how to build a database node using a Raspberry Pi. You will see how
the examples in this chapter concerning how and where the data is stored can be leveraged to make a robust
MySQL server using a low-cost computer. The process for installing MySQL on other boards is similar.
(^16) https://en.wikipedia.org/wiki/Database_normalization
(^17) http://dev.mysql.com/downloads/utilities/