In the past year or so I have thought a lot about how to design tables and columns within databases and there is nearly nothing that wouldn't get more robust by adding in a "valid_from" and "valid_till" and make it accept multiple values. Someone's name is Foo? What if they change it to Bar at some point and you need to access something from before with the old name?
If you have only a name field that has a single value that is going to be a crazy workaround. If your names are referencing a person with a date that is much easier. But you need to make that ddcision pretty early.
The tradeoff is that this is very expensive at the scale of large geospatial data models both in terms of performance and storage. In practice, it is much more common to just take regular snapshots of the database. If you want to go back in time, you have to spin-up an old snapshot of the database model.
A less obvious issue is that to make this work well, you need to do time interval intersection searches/joins at scale. There is a dearth of scalable data structures and algorithms for this in databases.
Anyone who works with human names should take a look at the HL7 V3 and FHIR data models, which were designed for healthcare. They support name validity ranges, and a bunch of other related metadata. It can be challenging to efficiently represent those abstract data models in a traditional traditional database because with a fully normalized schema you end up needing a lot of joins.
and if your schema doesn't change much, it's practically free to implement, much easier and simpler than copypasting audit tables, or relying on codegen to do the same.
The last time we did this, we basically hand-rolled our own, with a database trigger to insert data into a different table whenever an `UPDATE` statement happened.
But this seems like it's probably a better solution.
never had used pgaudit yet to vouch for it but have it on the backburner/log of things to try for such a use case!
I think the real magic is it lleverages the WAL (write ahead logs) from pg engine itself, which you could certainly hook up into too, but im not a db expert here
One that routinely surprises me is that this is not easy to do in any popular contacts application. I actually would like to keep every address I've ever had in there, in case I need to remember it for some reason later. Maybe I just want to reminisce. I don't want to accidentally have it as an "active" one, though.
If you have only a name field that has a single value that is going to be a crazy workaround. If your names are referencing a person with a date that is much easier. But you need to make that ddcision pretty early.