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

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 2

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