Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there any ready mechanism in Oracle, SQL Server, PostgreSql or OR framework (C#), which can version information in a database? By versioning informartion I mean that e.g. I have table Person (id, version, isEnable, enableFrom, enableTo, name, surname), and any change in column name or surname makes new row with incremented version, changed isEnable, enableFrom and enableTo.

share|improve this question

closed as not a real question by Jon Seigel, Mark Storey-Smith, Phil, Jack Douglas Mar 4 '13 at 9:42

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

4  
Undeclared cross-post on SO. If you post the same question in multiple places, this information must be in your question. The better would be to have the question migrated instead of duplicated. –  Erwin Brandstetter Mar 3 '13 at 21:09
1  
I guess this is more of a design pattern question. I prefer to use separate active and history tables in some circumstances. Doing so makes queries easier (no rubbish indexing on is_enabled columns etc) –  Phil Mar 4 '13 at 1:21
1  
The huge number of downvotes is probably because of the cross-post and because you've shown no sign that you did any research before posting. I still think it's a bit harsh. –  Craig Ringer Mar 4 '13 at 2:41
    
This is not a bad question and the DVs are a bit harsh, but it is way too broad. The solutions for the different RDBMSs are extremely diverse (eg 'flashback data archive' on Oracle). I suggest you focus on one platform per question and re-ask? –  Jack Douglas Mar 4 '13 at 9:42
    
Managing Time in Relational Databases -- 30+ parts –  ErikE Mar 4 '13 at 18:56

2 Answers 2

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).

share|improve this answer

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

share|improve this answer
    
No idea why you got downvoted, but this whole thread is weird. Good suggestion, though it might be worth explaining some of the performance, data management, and referential integrity consequences. –  Craig Ringer Mar 4 '13 at 2:43

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