How would you do to keep full history of every values of all tables in a DB, which can be queried with a specific date (for each and every column of all tables)? I mean: one would just need to specify a date for which the value was “active” or in “validity”.
In a dumb way, I would add to each table a “validity start date” column. And instead of copying every value, I would only copy values that changed, ad set all the other columns to NULL. However, this solution is really horrible, since it complexifies the queries, even if it takes less space.
Is there a specific technology (I am in the Microsoft world) that enables to do that, or a specific way to model the database to do that (except creating a table for each column of each table)?
I would like to mention that I am not a DBA but a dev. KR! KR!