0

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.

1

In Oracle 12c specifically there is a new feature called "temporal flashback queries" that serves exactly this purpose. (Similar features are available in other databases: DB2, SQL Server, Vertica to name a few.)

To enable temporal queries you:

  1. Ensure that each of the tables representing your document has a pair of dates indicating the start and end date (time) when a particular record was valid (active), and
  2. Keep historical records by not updating them in place but rather inserting new versions of the records, updating only the end dates of historical records.

You will then be able to issue queries against your tables that look much like SELECT whatever FROM mytable AS OF some_date, which would return the records that were active at the specified date.

You can read the detailed description here.

  • This sounds like a good solution, though it is very specific to oracle. Is there a pattern that would not tie me to one db vendor? – lordvlad Jul 15 '15 at 9:03
  • I don't think there is any other possible "pattern" -- you keep old versions of records, along with their effective start and end dates, and query tables using date ranges you're interested in. The syntactic sugar of temporal queries (with slightly different implementation by different vendors) makes it easier, but nothing prevents you from using pre-temporal query syntax to accomplish the same thing. – mustaccio Jul 15 '15 at 11:49
  • You might also be interested in reading about slowly changing dimensions. – mustaccio Jul 15 '15 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.