I have the challenge to design a new database using a RDBMS, Oracle, to be precise. In this database there will be one specific record/document which may span multiple tables.
With as little duplication as possible, I want to keep track of the changes made to this record, and offer a simple way to create altered copies of that record.
I don't want a history table that will sum up the changes in text, because I want to be able to fetch
document x exactly the way it looked at
time t. But I don't want to have a full copy for every history entry either.
Are there best practices or patterns how to achieve this?
I may have an idea how to solve this using a strategy mimicking
git, but I wanted some opinions first before I go that road.