Comment by bink-lynch

We implemented a "point-in-time" database architecture to accomplish similar behavior based on this document:

https://www.simple-talk.com/sql/database-administration/data...

The article describes two columns, dateEffective and dateEnd. If dateEnd is null, the row is still effective. When the row has been updated or deleted, the dateEnd field is set.

This was implemented in a regulated industry where we had to show the state of the system at given points in time. It worked well for this purpose.