Versioning data in Databases [closed]

Audit triggers are often sufficient for this, eg:

http://wiki.postgresql.org/wiki/Audit_trigger_91plus

... or follow Neil McGuigan's suggestion of using time range fields to simulate a temporal database using a normal RDBMS.

What you describe sounds simple at first, but then consider the following sequence of events, using the imaginary WITH (AUDIT) keyword to create a new row with incremented version instead of updating when an update is done.

CREATE TABLE a ( id SERIAL PRIMARY KEY, blah text NOT NULL ) WITH (AUDIT);
INSERT INTO a(blah) VALUES ('one'),('two'),('three');
ALTER TABLE a ADD COLUMN anewcolumn NOT NULL DEFAULT 'blech';
INSERT INTO a(blah) VALUES ('afteralter');
ALTER TABLE a(blah) DROP COLUMN blah;

How do you handle this when you're doing time-travel like auditing? The table no longer has the blah column, so how do you find the value of it for old rows? The old rows didn't have the column anewcolumn that now exists, and it's NOT NULL ... will you return NULL-containing rows anyway? And if so, how does the client tell the difference between "This field is NULL because the column did not exist at this pointand "This field isNULLbecause the column existed but the value was reallyNULL`".

This is why I chose to use a json representation of changes.

As for versioning, that's harder than you think too. How does the database efficiently get the "latest" version?

PostgreSQL actually used to do something like this back in the 6.x days with a feature called Time Travel, which (AFAIK) used the MVCC mechanism to keep old row versions and allow you to query them. It caused all sorts of performance and maintenance problems and was removed in late 6.x / early 7.x period (haven't checked exactly when).

sounds like you're looking for a temporal database

generally, it's as simple as adding a from_date and to_date (nullable) columns

example:

EMPLOYMENT
employee_party_id not null references party(id),
employer_party_id not null references party(id),
from_date timestamp not null,
to_date timestamp null
check (from_date <= to_date)
primary key (employee_party_id, employer_party_id, from_date)

For a Person, you might have born_at and died_at columns

Postgres has a temporal extension for some extra validation for this kinda thing