Chapter 5 ■ MySQL priMerStep 3: Design the Queries or Questions for the Database
Whenever designing a database, I make it a point, if not a required milestone, to determine what queries
I want to run on the data (in other words, the questions I want to ask and the answers I expect). These
questions are those that would be asked of an application designed to present the information to users.
Think of it this way: if you had a plant-monitoring system, what features or results do you expect to see in the
user interface? Defining these before you design your application will make the application development
proceed much more smoothly.
Queries we may want to perform on a plant-monitoring solution include but are not limited to the
following. As you will see, some of these are simple and easy, while others may take a more complex SELECT
statement to achieve. I will demonstrate some of these in the following sections, leaving the others for your
exploration.
- Which plants need watering?
- Which plants are experiencing the highest temperatures?
- What is the average soil moisture for each plant for each day?
- What is the temperature range that a particular plant endures during the
daylight hours? - How many plants are being monitored?
- How many plants are outside?
- What is the average temperature of the inside or outside plants?
The following sections walk you through how to create the queries for three of these questions. I present
the queries first without validation and later will walk through the same logical process showing you how to
test each query with known test data. However, for some of the more complex queries, I show you how they
work in context so that you can see how the parts fit together.
Example 1: A Simple Query
Let’s begin with a simple query. This one involves doing some aggregation. Recall one of the questions we
want to ask is, “What is the average temperature of the inside or outside plants?” In this case, let’s make it a
bit easier and query only for the plants that are outside. As you will see, it is trivial to change the query to find
the inside plants.
Like all good query design, we begin by breaking down the problem. For this query, we need to know
the average temperature for each plant. Recall we need to use a GROUP BY clause with the AVG() function.
But what if we want to restrict the rows to only the readings taken on a given day such as today? That is, our
application may provide a periodic status of the average temperature for each plant for the current day. We
could query the data over a range of times, but that is more of an analysis operation—not that you couldn’t
do that. Just to keep it simple, we’ll use today’s readings.
So, how would you query for such information? Clearly, we need to use some form of date check.
Fortunately, MySQL provides many such functions. For this, we will use the DATE() and CURRENT_DATE()
functions. These perform the operation of determining the samples that were taken today using the
event_time TIMESTAMP column. One of the really cool and powerful features of the database is if we use
these functions, we can create queries, views, functions, and so on, that automatically determine which rows
were recorded today. Thus, we don’t need to read, store, and transmit the date when doing queries using
these functions. Cool!