How to create practical manufacturing databases

Dennis Brandl, BR&L Consulting -- Control Engineering, 10/1/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.