Storing Procedure Fields That Were Used

Question

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.

Answer 1

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?

Answer 2

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

Answer 3

Concepts

It sounds like you are attempting to build a LIMS system.

The ones I have played with contained two sets of tables:

• One set of tables contains "what you plan to do".

• Another set of tables contains "what was actually done".

The two sets of tables are almost identical.

This schema design allows you to capture any extra Work that was done in addition to any changes from running a normal Procedure.

Examples

Let's say Step 3 of a Procedure required Machine B to be used. Machine B is broken. After Manager approved usage of Machine A, the entry in Actual Work Done will reflect that Machine A was used for Step 3 instead of Machine B. audit tables are usually involved along with references to the Manager's approval.

R&D runs would belong to the R&D Procedure. Any Work can be added to the Actual Work Done table without Manager's approval. These would have to have the user manually enter the values for the Work.

System Calibration runs would belong to the Calibration Procedure. Same goes for Start Up runs, "Let's show off how this $1M toy works" runs, and also "something is clogged in the machine, let's run blanks until it clears" runs.

Official runs work smoothly. The main difference between Official runs and R&D runs would be: Official runs have the correct parameters filled out for each Work in the form the user has to fill in. This allows the user to adjust the value in case the value wasn't 100% identical. eg - 1.001 mg of NaCl was used instead of the prescribed 1.000 mg of NaCl.

Conclusion

Overall, this is a variation of your Option 1.