as per request, I'll reduce this whole set of questions to 1-2 core points (thanks for the feedback ;)).
I'm considering using insert-only tables for keeping the history of my data. while the productive usage is pretty clear to me by now, I'm still struggling to find a solution for some problems from a continued development perspective:
The Problem I have is, how do I deal with a changing Schema without having to manipulate/delete/transfer old records elsewhere? cases that I'm currently considering:
- I get a new constraint, all current live data will be changed to fullfil that constraint according to some business rule. constraint gets added. however my old historical records may break this constraint, which forces me to do something with those entries (delete and copy elsewhere, also modify these) which I can't do as I need this data as they are for compliance reasons
- some datatypes change: I now only allow numbers in a field where previously Strings were allowed (its just an example, I know that I could also fix this specific example by some custom constraints instead of column datatype)
as I preferably want to deny any deletes and updates on the database level, keeping an admin access which could allow deleting old data via copying it elsewhere seems to break the idea behind a insert only table, no?
how would one deal with those situations in insert-only tables? create new table, rename old table to something else with a version number and now only use the new table? ... doesn't sound right, but idk what the best way is to deal with it.
so.. thanks for any help! :)