Chapter 5 ■ MySQL priMer
- Plan your queries: Always include the questions you want to ask of the database
when designing tables. Preparing the queries ahead of time will help with
development efforts later. - Avoid using SELECT : While it is easy to just get all the columns for a table, the use
of in the column specification can be suboptimal for large tables or tables with a lot
of columns. We saw this earlier in the complex example. The only columns needed
were the name and location. Thus, you should resist the temptation to get all the
columns and instead specify the columns you want. - Use lookup tables for fixed or seldom changed data: Similar to redundant data, using
static (or seldom updated) tables can help reduce the amount of extra data you are
passing in result sets and storing. It is much more efficient to store a shorter integer
or similar key than to store one or more fields throughout the table. - Use the power of the database server whenever possible: Try to think of ways to offload
the processing of aggregates, mathematical functions, and especially computational
expensive tasks such as date and string manipulation to the database server. The
power of views, functions, triggers, and so on, cannot be understated. It may take
some time to become proficient in these concepts, but the payoff can mean using
smaller, cheaper components in your IOT network nodes. - Use good, coherent names: Try to use as descriptive and coherent names in your
database objects. Resist the temptation to save coding keystrokes by using a, b, c,
and so on, as object or column names. If someone other than you tried to figure out
what the data describes, they’d be completely lost. It is better to be slightly verbose
than terse. Finally, avoid using acronyms or nonstandard abbreviations, which can
obfuscate the meaning. For example, what is kdxprt? Kid expert? Nope. I had to ask the
designer—it means (in a politically correct manner), “former parent.” Don’t do that. - Use primary keys on master tables: For any data that can be uniquely identified, use a
primary key that can uniquely identify each row. You can use multiple columns in a
primary key. For tables where the columns do not uniquely identify the row, you can
add a surrogate or artificial primary key using an AUTO_INCREMENT integer data type. - Avoid wide tables: If your table contains 20 or more columns, it is likely it is poorly
designed. More specifically in database terms, it is not normalized. Look at the data
again and determine whether the columns used can be moved to another table. Look
for columns that are reference in nature, do not change frequently, or are redundant.
• Do not throw away data: You should always retain the raw data in your tables. If your
database stores sensor readings, store the raw values. If you need a calculated column
to make reading or queries easier, it is OK to store them like I showed you in the plant-
monitoring database, but try to make these columns simple and use a trigger to set the
value rather than code on your data or sensor nodes. This isolates the code to a single
location and once tested can be relied upon. Having the raw data will allow you to plan
queries in the future that you may not have considered requiring the raw data.- Avoid storing binary data: While database systems allow you to store large, binary data
(in MySQL a BLOB or binary large object), they are not efficient and a poor choice for
tables that store many rows this way, especially if they do not change. For example, if
you wanted to store a photo associated with a data item (a row in your master table),
you should consider making a field to store a path to the image and store it on the
database server. While this creates a new problem—changing the path—it removes the
BLOB from your table and can make queries a bit more efficient.