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.

10/01/2005


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, dbrandl@brlconsulting.com , is the president of BR&L Consulting, a consulting firm focusing on manufacturing IT solutions, based in Cary, N.C.




The Engineers' Choice Awards highlight some of the best new control, instrumentation and automation products as chosen by Control Engineering subscribers. Vote now (if qualified)!
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.
HMI effectiveness; Distributed I/O; Engineers' Choice Award finalists; System Integrator advice; Inside Machines
Women in engineering; Engineering Leaders Under 40; PID benefits and drawbacks; Ladder logic; Cloud computing
Robotic integration and cloud connections; SCADA and cybersecurity; Motor efficiency standards; Open- and closed-loop control; Augmented reality
Programmable logic controllers (PLCs) represent the logic (decision) part of the control loop of sense, decide, and actuate. As we know, PLCs aren’t the only option for making decisions in a control loop, but they are likely why you’re here.
This digital report explains how motion control advances and solutions can help with machine control, automated control on assembly lines, integration of robotics and automation, and machine safety.
This article collection contains several articles on how advancements in vision system designs, computing power, algorithms, optics, and communications are making machine vision more cost effective than ever before.

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

Control room technology innovation; Practical approaches to corrosion protection; Pipeline regulator revises quality programs
Cloud, mobility, and remote operations; SCADA and contextual mobility; Custom UPS empowering a secure pipeline
Infrastructure for natural gas expansion; Artificial lift methods; Disruptive technology and fugitive gas emissions
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.
The Engineers' Choice Awards highlight some of the best new control, instrumentation and automation products as chosen by Control Engineering subscribers. Vote now (if qualified)!
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.
HMI effectiveness; Distributed I/O; Engineers' Choice Award finalists; System Integrator advice; Inside Machines
Women in engineering; Engineering Leaders Under 40; PID benefits and drawbacks; Ladder logic; Cloud computing
Robotic integration and cloud connections; SCADA and cybersecurity; Motor efficiency standards; Open- and closed-loop control; Augmented reality
Programmable logic controllers (PLCs) represent the logic (decision) part of the control loop of sense, decide, and actuate. As we know, PLCs aren’t the only option for making decisions in a control loop, but they are likely why you’re here.
This digital report explains how motion control advances and solutions can help with machine control, automated control on assembly lines, integration of robotics and automation, and machine safety.
This article collection contains several articles on how advancements in vision system designs, computing power, algorithms, optics, and communications are making machine vision more cost effective than ever before.

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

Control room technology innovation; Practical approaches to corrosion protection; Pipeline regulator revises quality programs
Cloud, mobility, and remote operations; SCADA and contextual mobility; Custom UPS empowering a secure pipeline
Infrastructure for natural gas expansion; Artificial lift methods; Disruptive technology and fugitive gas emissions
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.
The Engineers' Choice Awards highlight some of the best new control, instrumentation and automation products as chosen by Control Engineering subscribers. Vote now (if qualified)!
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.
HMI effectiveness; Distributed I/O; Engineers' Choice Award finalists; System Integrator advice; Inside Machines
Women in engineering; Engineering Leaders Under 40; PID benefits and drawbacks; Ladder logic; Cloud computing
Robotic integration and cloud connections; SCADA and cybersecurity; Motor efficiency standards; Open- and closed-loop control; Augmented reality
Programmable logic controllers (PLCs) represent the logic (decision) part of the control loop of sense, decide, and actuate. As we know, PLCs aren’t the only option for making decisions in a control loop, but they are likely why you’re here.
This digital report explains how motion control advances and solutions can help with machine control, automated control on assembly lines, integration of robotics and automation, and machine safety.
This article collection contains several articles on how advancements in vision system designs, computing power, algorithms, optics, and communications are making machine vision more cost effective than ever before.

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

Control room technology innovation; Practical approaches to corrosion protection; Pipeline regulator revises quality programs
Cloud, mobility, and remote operations; SCADA and contextual mobility; Custom UPS empowering a secure pipeline
Infrastructure for natural gas expansion; Artificial lift methods; Disruptive technology and fugitive gas emissions
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