What's difference between a temporal database and a historical archive database ?

Question

It is said here:

http://www.ibm.com/developerworks/web/library/wa-dbdsgn2.html

Each table in the DB should have a history table, mirroring the entire history of the primary table. If entries in the primary table are to be updated, the old contents of the record are first copied to the history table before the update is made. In the same way, deleted records in the primary table are copied to the history table before being deleted from the primary one. The history tables always have the name of the corresponding primary one, but with _Hist appended.

In temporal db see here http://stackoverflow.com/questions/1514858/temporal-database-modeling-and-normalisation there isn't a separate table as far as I understand.

So when should I create another table or not ?

Answer 1

History tables provide a history of (generally non-temporal) changes made to the primary database records by users. This history is archival in nature (i.e. accessed occasionally for historical purposes). The temporal information (when the change was made) is secondary in nature.

A temporal database is designed specifically to execute time queries against. The temporal information is primary in nature, and kept online for immediate retrieval. A second table is not created, unless archiving also needs to take place.

http://en.wikipedia.org/wiki/Temporal_database

Answer 2

What Robert said theoretically - nothing to add.

Practically, temporal table vs. main+hist table, has other impications.

For heavily maintained data (e.g. updates/deletes greatly outnumber the inserts), having a historical (sometimes also referred to as "audit" - as it is the main mechanism to enforce audit trail of DB data) table allows keeping the main table reasonably small sized compared to keeping the audit info inside the main table itself. This can have significant performance implications for both selects and inserts on the main table, especially in light of index optimization discussed below.

To top that off, the indices on hist/audit table do not need to be 100% identical to main table, meaning you can omit indices not needed for querying audit data from hist database (thus speeding up inserts into audit table) and, vice versa, optimize what indices there are to specific audit queries you have (including ordering the table by timestamp via clustered index) without saddling the main table with those indices which slow the data changes (and in case of clustering on time of update, clash with main table's clustered index so you usually can't have it clustered in temporal order).

Answer 3

The history table that is talked of in that developerworks article is a table that holds the history of the database (i.e. the history of our beliefs about reality).

The kind of history that you asked about in that other thread holds our (current !) belief about the history of reality.

Note the difference. The two concur only to the extent that our past beliefs about reality have indeed been correct. And that is not always 100%.

If you use the former as being the latter, then you are in a sense assuming that that degree of concurrence is indeed 100%, i.e. that all your past beliefs about reality always and by definition coincided with reality, i.e. you are assuming that it is impossible for you to have had any faulty belief about reality.

Tables that hold the history of other tables can suit purposes of auditing. Tables that hold the history of reality can suit the purpose of any user that is interested in that historical information.