Chapter 5 ■ MySQL priMer
What aBOUt ChaNGING OBJeCtS?
you may be wondering what you do when you need to modify a table, procedure, trigger, and so on.
rest easy, you do not have to start over from scratch! MySQL provides an ALTER command for each
object. that is, there is an ALTER TABLE, ALTER PROCEDURE, and so on. See the online reference manual
section entitled “Data Definition Statements” for more information about each ALTER command.Planning Database Storage for IOT Data
Now that you know how to get, install, and use MySQL, it’s time to focus on how to apply what you learned in
previous chapters and set up a database for storing IOT data. Recall IOT data can be any form of sensor data,
personal information, codes, dates of events, identification of devices, and so on.
I present this topic by way of example. More specifically, I feel it is best to demonstrate database design
rather than dictate practice and policy by rhetoric. I think the examples show many of the concepts and
constructs you are likely to encounter when designing your own database(s) for your IOT solutions. Let’s
dive in with a complete design for a plant-monitoring solution.
The following may seem familiar since I used some primitives of these tables in previous sections.
However, this section contains a fully developed database design. This is just one possible design I could
have used. I challenge you to consider alternatives should you consider implementing your own
plant-monitoring solution.
COrreCt DataBaSe DeSIGN: aM I DOING thIS rIGht?
there isn’t really any wrong way to design your database. While some DBas would cringe at such a
claim, so long as you can achieve all your goals with reasonable performance and no loss of data,
you should consider your design feasible. after all, even the most complex and professionally design
databases go through routine and evolutional changes. that is, you don’t have to get it right on the first
go. you can always adapt your database to your growing and maturing iOt solutions needs.Example 1: Plant-Monitoring System
Let’s explore an IOT solution that monitors ambient temperature and soil moisture for plants. In this case, the
solution is designed to support any number of plants (the target data object). A key component of this IOT
solution is that all the data collected is stored where the sensors employed are read about once every hour.
In the following example, I show you four basic steps that I like to use when designing a database. You
may find other philosophies that have more steps with more rigid processes (and that’s great), but for the
enthusiasts and hobbyists, I recommend using these simplified steps. That doesn’t mean you cannot design
any other way, just that this method should work for most. Indeed, if you have experience in designing
databases, you should see parallels with your own methodology.