Storing Procedure Fields That Were Used

best practicesdatabase-designdenormalizationnormalization

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:

  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.

Best Answer

I'm assuming you could have multiple exceptions for a given work/procedure pair, with each exception potentially modifying one or more different procedure fields.

While you could certainly store each modification in its own row (an 'override'), any queries attempting to get the 'current' settings, or a historical view, is going to get complicated (especially since the current design - as presented - doesn't appear to have any means of showing the order in which changes were applied).

Based solely on the info provided, I would probably opt for a typical history/audit solution:

  • maintain the 1-1 relationship between Work and Procedure

  • when an exception is required you update Procedure with the new value(s)

  • a trigger on Procedure will write the old record to an audit/history table (eg, Procedure_hist) with the same columns plus one additional column to designate an ordering (eg, seq_no, modification_datetime, etc)

  • when you want to see the current Work/Procedure config you join Work and Procedure, eg:

    select ...
from   Work w
join   Procedure p
on     w.Procedure_id = p.Procedure_id

  • if you need to see a historical/audit view then you can pull in the Procedure_hist table, perhaps as a outer join, eg:

    select ...
from   Work w
join   Procedure p
on     w.Procedure_id = p.Procedure_id
left
join   Procedure_hist h
on     w.Procedure_id = h.Procedure_id

This is a slight variation on your Option #2. Yes, it means some duplication of data but it also allows for easier coding (updating, retrieving), which in turn will likely make it a bit easier to maintain down the road (especially if someone comes along behind you to maintain the system). [The K.I.S.S. principle comes to mind.]

Without knowing more about the use cases, upstream/downstream requirements, etc ... I'd probably want to see if it made sense to maintain the special testing with the same Work-Procedure-Procedure_hist relationship.

You could add a flag to designate if the Work (or Procedure?) is a standard or special case.

Other considerations that could affect the model ... is Work-Procedure a strictly one-to-one relationship or could there be a many-to-one/many-to-many relationship, eg:

  • could a single Procedure be (re)used by different Work efforts?

  • could a single Work effort be broken into multiple Procedures?

Related Question