Thanks for the hint. Your solution seems to be good. However, I am
designing my framework for fun, to give back something to the
community and because I think it's time that historisation is not
reinvented and reimplemented again and again.
Having that said, I think I can get rid of the need to cascade changes
up and down the refential constraints by the following modelling. I am
aware that I would restrict the use of the framework to this model.
------------- --------------------
==> | ENTITY_HEAD |>--..| PARENT_ENTITIY_HEAD |
// ------------- --------------------
// |
------------ // |
==> | ENTITY_ACT | == |
------------ \\ |
\\ /|\
\\ -------------
==> | ENTITY_HIST |
-------------
The head table only contains the business key, the business surrogate
key and foreign key attributes to parent tables. Strictly, I can
contain also payload attributes that never ever ever ever ever ever
change their value. But who is to guarantee that? However, It is SCD0
so once a value is entered it never changes.
The history table contains all the other attributes the payload so to
speak and get the historisation. Version matching between related
entities could be done by a generated view so no one would have to
re-invent the wheel.
One could generate the head table from the business key in the actual
table and generate the hist table from the actual table without the
business key and a template table with history attributes. Can one
exclude attributes from inheritance?
Quoting Jeremy Finzel <finzelj@xxxxxxxxx>:
If I follow your use case, we have written something that just may
fit your scenario and plan to open source it rather soon.
It has several layers but let me boil it down. First we use an
open sourced auditing system to log changes to the source tables.
This becomes your queue. A postgres background worker will
asynchronously process these changes based on your configuration,
which is highly configurable. It also handles the concurrency you
are questioning.
This allows you to build history tables without requiring you for
example to do it directly via a trigger. It also removes redundancy
if you have the same key updated multiple times. It assumes we are
fine with the data built not being 100% up to date data because
these updates obviously don’t all happen in the same transaction as
the source data change.
Let me know if this interests you and I can share more.
Thanks,
Jeremy
