• Regarding EF:
    • is most easily implemented as a Type 4 process…with modifications.
    • Although correct, a Type 4 appraoch is not usable without additional work:
      • Any FK value will be pointing at a Reference table, not the ReferenceHistory table.
      • Yes, there is a Date range in the ReferenceHistory…but it requires heavyness when querying, to isolate the Reference record's activity window within which in the principle Entity's last modified date sits….
      • Options are to add to the Entity record a second FK column (FK2) column, that points to the RefHistory table.
        • But it has to be nullable, and filled out of process.
        • Which has a risk of causing errors.
        • Integrity between Record and ReferenceRecord has to be broken (ReferenceRecord can be deleted) unless the Reference data record is only Soft deleted.

Therefore, consider the following requirements:

  • All mutable entity and reference data tables need to be shadowed with their respective History table.
  • A second FK column has to be added to all History tables, pointing to History Reference tables.
    • The secondary FK MAY be nullable if intended to be processed out of band.

    * Reference Data must be Soft Deleted (in order to not cause Referential failure from History table).