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.

My system receives data feeds. Each data feed will end up creating inserts and/or updates to most tables in the (relational) database.

I need to capture the snapshot of what the entire database looked like after each data feed is received. Basically I need a way to version the database each time a data feed is run through the system.

Note, by capturing a snapshot, I dont mean literally taking a snapshot of the database, but rather writing history records or some such mechanism so that I can query the database across "versions" to see what changed between versions (among other use cases)

Do known data model designs exist that can capture a snapshot of a database version like this?

share|improve this question
    
Is this a relational database? –  ryan1234 Apr 23 '13 at 22:47
1  
Which DBMS are you using? Oracle has "flashback" queries and DB2 (10.1) has "time travel" queries. Both store historical data in separate tables, so the performance of queries for the "current" data is not affected –  a_horse_with_no_name Apr 24 '13 at 6:35
    
Want the answer to be database agnostic for the major relational databases -- mysql, postgres, oracle. I use several databases that all need to do this –  BestPractices Apr 24 '13 at 13:07
add comment

migrated from stackoverflow.com Apr 24 '13 at 4:51

This question came from our site for professional and enthusiast programmers.

2 Answers

NHydrate will do this for you, and I'm fairly certain many other ORM tools also provide this functionality.

As a roll your own effort, there are a bunch of ways to do this but here is a quick example: SQL Server 2005 history tables using triggers.

Then you just need a set of queries to retrieve the last record prior to the time of the data feed update.

share|improve this answer
add comment

what you are looking for is slowly changing dimension design. each row of data in the table will hold the opening date and the closing date for that record. this deisgn enables you to query the table as it was at any point in time in the past.

example

create table scd (
    id          number , 
    open_date   date, 
    close_date  date, 
    data        varchar2(10), 
    primary key (id , open_date)
)

alter session set nls_date_format='dd/mm/yyyy';    

insert into scd values (1,'01/01/2013','03/01/2013','D1');
insert into scd values (1,'03/01/2013','04/01/2013','D1.1');
insert into scd values (1,'04/01/2013','31/12/9999','D1.3'); // this is the current occurrence

insert into scd values (2,'01/01/2012','31/12/9999','D2'); 

insert into scd values (3,'01/01/2012','31/12/9999','D3'); 
insert into scd values (3,'01/01/2012','03/01/2012','D3.closed'); // this is a closed occurrence

// querying the data as of 02/01/2013
select * from scd where '02/01/2013' => open_date and '02/01/2013' < close_date

// querying currently open occurrences
select * from scd where sysdate between open_date and close_date

hope this helps

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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