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.




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.
The Engineering Leaders Under 40 program identifies and gives recognition to young engineers who...
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.
Sensor-to-cloud interoperability; PID and digital control efficiency; Alarm management system design; Automotive industry advances
Make Big Data and Industrial Internet of Things work for you, 2017 Engineers' Choice Finalists, Avoid control design pitfalls, Managing IIoT processes
Engineering Leaders Under 40; System integration improving packaging operation; Process sensing; PID velocity; Cybersecurity and functional safety
This article collection contains several articles on the Industrial Internet of Things (IIoT) and how it is transforming manufacturing.

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

SCADA at the junction, Managing risk through maintenance, Moving at the speed of data
Flexible offshore fire protection; Big Data's impact on operations; Bridging the skills gap; Identifying security risks
The digital oilfield: Utilizing Big Data can yield big savings; Virtualization a real solution; Tracking SIS performance
click me