Real-Time Manufacturing Database Architecture

Today's control engineer has access to computing power not envisioned a little more than a decade ago. Comprehension of basic manufacturing database architecture can significantly benefit design, development, and manufacturing process requirements.

Over the past five years, the engineering- and business-driven need for manufacturing process data has led to a preference for a structured and modular approach to develop manufacturing information systems, with the focus on the relational database

Expectations for maintenance of an installed plant process information system are the same as for a control process software system. That is to say, the same in-house control-oriented personnel must understand and troubleshoot the plant process information system, without need of an IT expert available 24/7.

The primary design criteria for manufacturing information system projects are: documented code, plant maintainability, customer ownership, open database connectivity, and future product obsolescence. Based on these criteria, three essential building blocks in this architecture are: process database server, process data collector, and Web-based reporting.

Process data server

As shown in the graphic at right, the process data server is a database server and an Internet/intranet Web server. The process data server can be located in the front office computer room or offsite via secure Internet. Memory and storage requirements for most manufacturing facilities are a nominal 512 MB and 40 GB of storage. The system archives data older than six months.

To support the needs for production and process information and succeed in the design/build of a manufacturing database requires a level of communication and cooperation between the IT department and control engineers not commonly exercised. The reason: computer and software skills needed to set up a manufacturing database are the same basic skills that a database administrator would possess to design and build a financial, inventory, or contact database. When comparing the development of manufacturing database to other types of database projects, the differences begin with conception and design. It is here the input of the control engineer is needed to identify the origin of the data and format it into proper engineering units or production metrics so that it can be understood by production management.

Plant-floor controllers automatically feed data to a manufacturing database. This can be done with a direct communication link to a PLC or controller in a DCS. With a legacy system or a microprocessor (compiled code) controller, an alternative approach is to place sensors in key locations to transfer manufacturing data via an industrial network to the database. Manufacturing database design can provide a marketplace edge if the information is used to advance business goals. To successfully design the database, one needs to understand table definition—where database views are needed and how to write stored procedures. When a query is made for a report, if it takes more than a few seconds to come up on screen, it will likely be too slow and require some redesign.


Control engineers should have an essential role in determining minimum essential criteria for speed and quantity of data. In a homogenization process, for instance, the record for the process stored in the manufacturing database need only have a high temperature, low temperature, and an average temperature logged for a certain time interval. The control engineer should write PLC code to provide these parameters to the process data collector.

Process data collector
Though the database server may be designed and built by the IT staff, the data collector resides on the plant floor and is therefore typically the responsibility of the control engineer.

The collector is the data acquisition point for all manufacturing data. Its essential function is to collect data from plant-floor controllers, store data in a "lite" version of the manufacturing database, and provide transaction management of records to the process data server.

The manufacturing database in the collector will store real-time data values in the same table structure and data formats as the process data server. The control engineer can take responsibility for data in the collector and the database administrator can take responsibility for data in the process data server. In this way, the manufacturing database becomes common ground for IT and control engineers to support the needs for plant information.

The server can be an industrial computer located in proximity to the manufacturing process; a NEMA 4/12 enclosure is recommended. The collector "gray box" has three connections: Ethernet (to server and other plant computers), high-speed industrial network interface (Modbus, DeviceNet, etc.), and power. A number of excellent drivers on the market can help get real-time data from PLCs directly into the process data collector database.
By using a Web server, such as Microsoft IIS (Internet Information Services) Server, to provide diagnostics and essential reports, the control engineer can ensure data integrity between the process data server and the collector.

When the network connection between the data collector and data server is lost or blocked, data transactions are stored on the data collector and rolled forward to the data server once the connection is re-established. Typically, the process data collector is configured to store three shifts of data.

Web-based reporting
End-users of manufacturing information are typically: plant managers, quality control staff, and production supervisors. I've found that the most commonly desired reports are the "bottom line" and the "deliverables" from the manufacturing database. Following are some examples of reports this architecture can provide:

-Quality data;
-Downtime reports;
-Compliance reports for shipments;
-Work order system with ties into time management software;
-Ingredient usage reports;

Reports are provided via an Internet browser. When the plant manager points the Web browser at the process data server, an information portal becomes available.

The report can be thought of as a "process database record," providing an information snapshot for a selected set of parameters, such as shift, batch, or work order. -and Production reports on a per shift basis.

Under the hood on the process data server, where the programming and configuration exist for reports, the Web-based server runs active server pages, with Visual Basic scripts often triggering stored procedures in the database to bring up the desired information.

Web-based reporting, constructed in this way, requires no applications to be loaded on the desktop (or additional licenses purchased), and information is accessible over an intranet or secure Internet connection.

Control Engineering Daily News Desk
Walter J. Cholawsky
Program4 Engineering

The news and views published here are not those of the automation4me.com. The contents are reproduced from the sources (kind courtesy) mentioned. This is intended only to generate interest in the original works