Hi, George.

I think manufacturing in general is one of the more complex things. We have a Manufacturing Control application that was born in FoxBase+ and had a lot of reincarnations trough all this time.

A few years ago the last version for DOS (FPD 2.6) was translated to the brand new VFP 5 and I was in charge of the redesign (I have had not participated much in the application since the first versions).

I found a table that stored the machine used in a certain task within a product specification. The primary key was:
Product code
+ Route number (a product can have many)
+ Revision number (a rout can have many)
+ Profile type (within the same revision: normal, reprocess, etc)
+ Step number (a first division of the production cycle)
+ Process number (second one)
+ Task number (third level)
+ Machine (and order number related to another list)

All this meant a primary key of 37 bytes! Of course this table was heavily accessed trought the control process (and this is the worst case I remember, but there where a lot more). Do I need to mention that one of the primary goals of the new version was to improve the sluggish performance?

We changed everything to surrogate keys and at the first implementations, all users commented that it was incredible how faster was Windows than DOS! 8-)

The speed improvements in almost any proceess averaged 400%.

Another -apparently- unrelated case happened many years ago was when a quite big customer (local subsidiary of a multinational company), received the order from headquarters in Korea of use a totally new set of product codes.

As the product code was our natural key everywhere, the conversion process was terrible! Thanks god we already had a data dicionary in which the program could be based. But it involved hundreds of tables with many thousands (sometimes millions) of records.

Since then, one of the first things in our internal handbook is USE SURROGATE KEYS!

Just my two argentine pesos to the PK debate.


>I've got a good one on this. How about this for a nightmare waiting to happen?
>
>As you know, I work for Shaw Industries, the largest manufacturer of broadloom carpet in the world. I've written over a dozen systems designed to monitor the efficiency of the manufacturing process. One of the principle processes, and one that applies to every order, is called tufting. Tufting is the process of sewing the yarn into the primary backing. Having a primary key in this instance is a necessity since the data is being stored on SQL Server.
>
>Now each plant enters the production information daily and, usually runs three shifts. So at minimum the natural key would be date and shift. That's not enough, however.
>
>Each plant has multiple tufting machines which are numbered. OK, so that would give us date, shift, and machine. Nope, that won't do it either. A given machine can run more than one order on a shift, so this won't uniquely indentify the record.
>
>Each machine can run more than one style of carpet. So this would give us: Date, shift, machine number and style. Naw...that won't work either. At a plant it is possible, during the course of a shift for multiple orders of the same style.
>
>So this would give us, at least, date, shift, machine, style, and order as the absolute minimum for the primary key based on the data. That's five different fields.
>
>As a result, I decided that an indentity column was the answer. Natural keys, anyone?