Storing Procedure Fields That Were Used I am creating a database for production work (specifically lab testing).
Most Work is for production, therefore 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 is 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:

Machine X was broken. Perform the Work by hand.
We ran out of Material Y. Use Material Z instead.
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 &quot;snapshot&quot; 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.
So this seems like largely a question about proper data normalization.

With data normalization in mind, we can easily rule out option 1. The data, like you suggest, will clearly be unnormalized and repeated.

Option 3 looks to be more of a hack to handle the current schema not capturing your domain. If you need to implement hacks like this, it's definitely time for a redesign.

Which brings us to Option 2. Of the three options, this seems to be the best. However, the schema seems like it could be capable of further proper normalization. Without knowing more about the domain, I would assume that there probably is some sort of functional dependency between Machine, Material, and RunTime. Or it could involve the product ID. Of course if all 4 of these values are free to differ without affecting the others then this area for schema optimization can be ignored.

If no functional dependencies exist in the procedure table, then the schema as suggested in option 2 with a new 'inactive' procedure to capture custom work appears to be the best overall choice