Food Engineering

Databases: The perfect complement to PLCs

February 3, 2010
If you’re concerned about pulling historical data for regulatory purposes, add a relational database to your PLC.


PLC data written to a database is used to create this downtime report. Source: Inductive Automation.
Most controls engineers have tackled PLCs and can program them with one hand tied behind their backs. So what’s the next logical challenge? According to Inductive Automation President Steve Hechtman, “Think SQL and relational databases (RDBs). You’d be amazed at the similarity,” he says. “It’s the next logical progression.

“You might ask how it is they’re even related. For one thing, RDBs can be [like] an extension of PLC memory,” says Hechtman. Live values can be mirrored in RDBs bi-directionally. Historical values and events can be recorded there as well. But operators and managers can interact with them too. “Over the last six years I’ve delved heavily into SQL and learned a lot about relational databases. I’ve discovered that working with SQL is remarkably similar to working with PLCs and ladder logic,” adds Hechtman.

SQL has four basic commands and about a hundred different modifiers that can be applied to each in various ways to achieve all types of results. Hechtman provides an example: Imagine effluent from a wastewater plant with its flow, pH and other things being monitored and logged. That’s what you typically see. But now associate these with other things: discrete lab results, the name of the persons who did the lab work, the lab equipment IDs and calibration expiration dates, who was on shift at the time and the shift just prior, their certification levels, what chemicals where added and when, who the chemical suppliers were, how long the chemicals sat before use and so forth. All of this becomes relational data, meaning that if it’s arranged properly in tables, users can run SQL queries to obtain all types of interesting results. The queries might provide insight into the most likely conditions that could result in an improper discharge so it can be prevented in the future.

“SQL is a high level language that isn’t very hard to learn, and you can be very clever with it,” adds Hechtman. He prefers to think of it as a natural extension of his PLC programming skills. SQL can be used to obtain or extract the history of a machine for the period it has been operating, and that history can be stored in a relational database. Furthermore, RDBs and SQL pull people and processes together. Machines don’t run alone. They’re merely part of a containing process, and that process was devised by people. SQL and relational databases form the bridge to integrate processes, machinery and people.

Hechtman doesn’t believe a commercial-off-the-shelf (COTS ) software package can fit an application any more than a COTS palletizer program can fit into different food processors’ applications. Every machine is different and every business process is different. That’s where SQL comes in. It has to duplicate or augment existing process flows, and these are intimately connected to the machinery.

To get started, Hechtman suggests picking up a “Dummies” type book. Then download and install the open-source MySQL database server along with the MySQL administrator and query browser. It only takes a few minutes to install, and then engineers can begin to experiment. “You can read about a LEFT JOIN or INNER JOIN, but typing one in and observing the results is worth about 1,000 words,” says Hechtman.