Sometimes a database is enough

With all the new information technologies available, it is important to remember that the simplest solution to a problem is often the best solution. Two projects recently reinforced this lesson for me. Both projects started with complex, Web-based solutions, but finished using simple databases. Databases often provide workable and robust solutions, and knowledge of database technology is vital for the modern control professional.

There are four types of databases—relational, network, hierarchical, and object-based. Most prevalent are relational databases using SQL (structured query language). In addition to commercially available SQL databases from Microsoft, IBM, Oracle, and others, there are several open source SQL offerings, such as PostgreSQL ( www.postgresql.org ), Firebird ( firebird.sourceforge.net ), and MySQL ( www.mysql.com ).

An application of mathematical set theory, relational databases were described in the 1970 paper “A Relational Model of Data for Large Shared Data Banks,” by Dr. E. F. Codd, in Communications of the ACM (Association of Computer Machinery). They are formally defined in ISO/IEC 9075:1992, “Database Language SQL,” and ANSI X3.135-1992 standards, but are usually referred to as SQL-92. There have been extensions to SQL defined in 1999 and 2003, but the basic relational model has remained the same.

In a relational database, data are collected into tables. Each table, known as a relation , contains columns, referred to as attributes , and rows, called tuples . Each row represents an object, and each column contains an attribute value for the object. Tables are related by values of attributes (hence the word relational). For example, two tables may have an equipment serial number attribute, and when two serial numbers are the same in the two tables, then the rows in the two tables have a relationship. This results in duplication of information, but a process called “normalization” is used during database design to ensure that the tables are effectively defined for minimal data duplication.

When you need to build a database for a manufacturing application, you will likely become involved with corporate database administrators; many companies have an IT group responsible for managing the structure of corporate databases. This group is responsible for ensuring that data are consistently defined, appropriate security is added to data creation and access, corporate rules for management of data are followed, and data duplication is minimized. If your database will be part of the corporate system, you will have to justify its existence to the satisfaction of the database group.

In some cases, much of the data needed for a manufacturing IT project is already available in corporate databases. For example, an asset management database with equipment definitions may exist that can be used to identify production equipment; or there may be inventory databases with material lot IDs and locations. When corporate data already exist, they should be used as part of your project. Usually a database “guru” knows about all of the corporate databases and is a valuable resource for finding data that already exist.

Corporate database administrators have a lot of practical experience that can be applied to manufacturing IT databases. However, you will have to describe the data and explain their meaning. It is vital to be clear about the name and meaning of tables and attributes, and to document the names and meaning in the appropriate corporate documents. This prevents others from misusing your data and you from misusing others’ data. Database administrators understand normalization and will help you design efficient database structures. However, talking with them will require an understanding of SQL. Modern control professionals should have at least a basic understanding of SQL and relational databases so they can apply simple solutions to simple problems.

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, NC

Do you have experience and expertise with the topics mentioned in this content? You should consider contributing to our WTWH Media editorial team and getting the recognition you and your company deserve. Click here to start this process.