How to create practical manufacturing databases

Based on the response received to my "Sometimes a database is enough" column, I thought it would be helpful to delineate a practical approach to develop such databases in cases where an MES system cannot be justified. Typical database applications involve recording an event, a person, a place, a material, and a time.


Based on the response received to my "Sometimes a database is enough" column, I thought it would be helpful to delineate a practical approach to develop such databases in cases where an MES system cannot be justified.

Typical database applications involve recording an event, a person, a place, a material, and a time. For example, tracking production artifacts—such as test labels and samples—as they move across desks for review and sign-off. A database application can allow an engineer or operator to scan his badge, the location, and folder containing the artifact. This allows tracking of artifacts as they move through the sign-off process.

Here are 10 simple guidelines for database applications:

  • Keep the functionality small and stick to problems that are transactional in nature and not real-time or procedural . Transactional problems rarely need more functionality than create, report, update, and delete.

  • Use an SQL-based database. This provides scalability and long-term maintainability of the application. Many manufacturing applications will have a long lifetime and only a standards-based database will provide long-term supportability. SQL databases also have standard reporting and display tools, reducing the application code.

  • Use a database server. While small applications often can be run on one computer, locating the database on a database server provides a supportable solution. The application's database may be just one of many databases on the server, but the server will have backup support, RAID (redundant) disks, and redundant power supplies.

  • Use integrity constraints. Most SQL databases support some form of foreign key, not null, unique, and range checking functions. This ensures that only valid data is added to the database and provides checks that do not have to be included in code.

  • Create tables without integrity constraints to hold "abnormal" situations. If an error occurs in normal operation, the operator often will not have enough information to solve the problem. Create "abnormal" tables that have the same structure as normal tables, but without integrity constraints, and store the abnormal cases in these tables. Use this information for analysis and correction of problems.

  • Use transactional control. Most SQL databases provide some form of transaction control. This allows multiple changes to the database that either all succeed, or if any one fails, then all roll back. This further reduces the application's error-handling code.

  • Use stored procedures. Stored procedures are SQL statements executed on database events, such as a change to a data field or deletion of a record. Stored procedures can significantly reduce the application code.

  • Provide an option so the application can create the database. Testing and rollout is much easier if the application can recreate its database, including the stored procedures. It is easier to track one application source file than multiple files and versions.

  • Don't hard-code database paths, instead use the OS registry database to find environment information. Hard-coded paths, such as "C:My Database," provide an execution constraint that is hard to maintain. Relocating the database without recompiling the application is more supportable.

  • Use Visual Basic or an equivalent to reduce code and provide a good user interface. There are many free, or low-cost, high-level development environments that can reduce the coding effort and make a database application a cost-effective solution.

These simple guidelines have helped me develop long-lasting database applications when an MES system is not available or cost justified.

Author Information

Dennis Brandl, , is the president of BR&L Consulting, a consulting firm focusing on manufacturing IT solutions, based in Cary, N.C.

No comments
The Engineers' Choice Awards highlight some of the best new control, instrumentation and automation products as chosen by...
The System Integrator Giants program lists the top 100 system integrators among companies listed in CFE Media's Global System Integrator Database.
Each year, a panel of Control Engineering and Plant Engineering editors and industry expert judges select the System Integrator of the Year Award winners in three categories.
This eGuide illustrates solutions, applications and benefits of machine vision systems.
Learn how to increase device reliability in harsh environments and decrease unplanned system downtime.
This eGuide contains a series of articles and videos that considers theoretical and practical; immediate needs and a look into the future.
Additive manufacturing benefits; HMI and sensor tips; System integrator advice; Innovations from the industry
Robotic safety, collaboration, standards; DCS migration tips; IT/OT convergence; 2017 Control Engineering Salary and Career Survey
Integrated mobility; Artificial intelligence; Predictive motion control; Sensors and control system inputs; Asset Management; Cybersecurity
Featured articles highlight technologies that enable the Industrial Internet of Things, IIoT-related products and strategies to get data more easily to the user.
This article collection contains several articles on how automation and controls are helping human-machine interface (HMI) hardware and software advance.
This digital report will explore several aspects of how IIoT will transform manufacturing in the coming years.

Find and connect with the most suitable service provider for your unique application. Start searching the Global System Integrator Database Now!

Infrastructure for natural gas expansion; Artificial lift methods; Disruptive technology and fugitive gas emissions
Mobility as the means to offshore innovation; Preventing another Deepwater Horizon; ROVs as subsea robots; SCADA and the radio spectrum
Future of oil and gas projects; Reservoir models; The importance of SCADA to oil and gas
Automation Engineer; Wood Group
System Integrator; Cross Integrated Systems Group
Jose S. Vasquez, Jr.
Fire & Life Safety Engineer; Technip USA Inc.
This course focuses on climate analysis, appropriateness of cooling system selection, and combining cooling systems.
This course will help identify and reveal electrical hazards and identify the solutions to implementing and maintaining a safe work environment.
This course explains how maintaining power and communication systems through emergency power-generation systems is critical.
click me