IT:AD:CRUS

• 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).