#StackBounty: #database-design #normalization #best-practices #database-denormalization Storing Procedure Fields That Were Used

Bounty: 100

I am creating a database for production work (specifically lab testing).

Most Work is production and performed strictly according to the Procedure for that Product. By itself, this is easy to model. The Work references the Procedure as it contains how the work was done:

Example Schema
Work:      Work_id, Procedure_id, {other non-relevant fields}
Procedure: Procedure_id, Product_id, Machine_id, Material_id, RunMinutes

Two exceptions (overrides and special testing) add much complexity to the design.

Question: Given the two exceptions below, how should I store the Procedure fields that were actually used for each Work?


Exception – Overrides:

Sometimes the required equipment or components are not available. In these cases, the manager can approve a one-time override for equivalent substitutes. Examples:

  1. Machine X was broken. Perform the Work by hand.
  2. We ran out of Material Y. Use Material Z instead.
  3. Keep runtime at 45 minutes

The database must capture how the Work was actually performed.

I see three possible options:

Option 1: Store Locally: The Work references the original Procedure. Each Work also locally stores the Procedure fields used, including any modifications. This creates many duplicates, but you have a local “snapshot” for each Work.

Example Schema
Work_id | Procedure_id    | Machine | Material | RunMinutes
1       | 1               | By-Hand | Z        | 45  

Procedure_id | Product_id | Machine | Material | RunMinutes
1            | 1          | X       | Y        | 45

Option 2: Single Use Procedure: The original Procedure is copied to a new Procedure, marked inactive, and modified with the overrides. The Work then references the new Procedure. This maintains the Work.Procedure_id for how the Work was performed.

Example Schema
Work_id | Procedure_id
1       | 2

Procedure_id | Product_id| Active| Machine | Material | RunMinutes
1            | 1         | Y     | X       | Y        | 45
2            | 1         | N     | By-Hand | Z        | 45

Option 3: Store as Overrides: The Work points to the Procedure and optionally points to a ProcedureOverride table. For each field in Procedure, if there is an override, then use it otherwise, use the Procedure value.

Example Schema 
Work_id| Procedure_id| Override_id
1      | 1           | 1

Procedure_id| Product_id| Machine | Material | RunMinutes
1           | 1         | X       | Y        | 45

Override_id             | Machine | Material | RunMinutes
1                       | By-Hand | Z        | NULL  

Query: ActualWork
Work_id   |Procedure_id | Machine | Material | RunMinutes
1         |             | By-Hand | Z        | 45  

Exception – Special Testing:

For non-standard work (such as research and development), there is no specific Procedure. Again, the database again must capture how the Work was actually performed.

I see two options (equivalent to the respective options above)

Option 1: Store Locally: Each Work locally stores all Procedure fields used. The user must input values for each field.

Option 2: Single Use Procedure: A new Procedure is created, marked inactive, and populated by the user. The Work then references the new Procedure. This maintains the Work.Procedure_id for how the Work was performed.
 
Keep in mind however, there is no actual (real world) Procedure for the non-standard Work.


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.