Store TrialSetup - reworded

[S D]

I am creating a database for lab testing.

For most testing, each [Trial] references a [SpecRev], a paper document which defines the [SpecRevTrialSetup] (equipment, setup parameters,etc).

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


EXCEPTION 1 - OVERRIDES:

Sometimes the required equipment or components are not available. In these cases, the manager can approve one-time override for equivalent substitutes. Since the [Trial] no longer matches [SpecRevTrialSetup], I must store the modifications somewhere.

I see three possible options:

Option 1, Store Locally: The [Trial] references the original [SpecRev]. Each [Trial] also locally stores the [TrialSetup] fields used, including any modifications. This creates many duplicates, but you have a local "snapshot" for each [Trial].

Option 2, Single Use SpecRev: The original [SpecRev] is copied to a new [SpecRev], marked temporary, and modified with the overrides. The [Trial] then references the new [SpecRev]. This maintains the [Trial.SpecRev.SpecRevTrialSetup] for how the [Trial] was performed.

Option 3, Store as Overrides: The [Trial] points to the [SpecRev] and optionally points to a [SpecRevOverride] object. For each field in [SpecRev], if there is an override, then use it, and otherwise, use the [SpecRev] value.



EXCEPTION 2 - SPECIAL TESTING:

For special testing, there is no [SpecRev]. In this case, I see two options:

Option 1, Store Locally: Each [Trial] locally stores all [TrialSetup] fields used. The user must input values for each field.

Option 2, Single Use SpecRev: A new [SpecRev] is created, marked temporary, and populated by the user. The [Trial] then references the new [SpecRev]. This maintains the [Trial.SpecRev.SpecRevTrialSetup] for how the [Trial] was performed. However, special testing doesn't logically reference a [SpecRev]



QUESTION: Given these two special cases, how should I store the [TrialSetup] for each [Trial]?