It is said here:
据说在这里:
http://www.ibm.com/developerworks/web/library/wa-dbdsgn2.html
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.
DB中的每个表都应该有一个历史表，反映主表的整个历史。如果要更新主表中的条目，则记录的旧内容将在更新之前先复制到历史表中。同样，主表中已删除的记录在从主表中删除之前被复制到历史表。历史表总是有对应的主表的名称，但是附加了_Hist。
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 ?
那么我应该何时创建另一个表?
3 个解决方案
#1
7
What Robert said theoretically - nothing to add.
罗伯特在理论上说的话——没什么可补充的。
Practically, temporal table vs. main+hist table, has other impications.
实际上，时态表与main+hist表有其他含义。
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.
大力维护数据(例如更新/删除大大超过插入),有一个历史(有时也称为“审计”——因为它是DB的主要机制,实施审计跟踪数据)表允许保持主表合理大小相比,保持主要表本身内部的审计信息。这对主表上的选择和插入都有重要的性能影响，特别是考虑到下面讨论的索引优化。
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, * with main table's clustered index so you usually can't have it clustered in temporal order).
最糟糕的是,指数在嘘/审计表不需要100%相同的主要表,意味着你可以省略指数不需要查询审计数据从数据库嘘(因此加快插入审计表),反之亦然,优化指数有什么具体审计查询你已经通过时间戳(包括订购表通过聚集索引)没有主表背上那些缓慢的指标数据的变化(在集群上的更新,与主表的聚集索引发生冲突，因此通常不能按时间顺序聚集它)。
#2
6
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
http://en.wikipedia.org/wiki/Temporal_database
#3
1
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).
在这篇developerworks文章中讨论的历史表是一个包含数据库历史的表(即我们关于现实的信念的历史)。
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%.
注意区别。这两种观点的一致之处在于，我们过去对现实的看法确实是正确的。而这并不总是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.
如果使用前者是后者,那么你就在某种程度上假设,赞同度确实是100%,也就是说,所有你的过去有关现实的信仰总是伴随着现实定义,即假设不可能你对现实有任何错误的信念。
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.
保存其他表历史的表可以适合审计的目的。包含现实历史的表可以适合任何对历史信息感兴趣的用户。
#1
7
What Robert said theoretically - nothing to add.
罗伯特在理论上说的话——没什么可补充的。
Practically, temporal table vs. main+hist table, has other impications.
实际上，时态表与main+hist表有其他含义。
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.
大力维护数据(例如更新/删除大大超过插入),有一个历史(有时也称为“审计”——因为它是DB的主要机制,实施审计跟踪数据)表允许保持主表合理大小相比,保持主要表本身内部的审计信息。这对主表上的选择和插入都有重要的性能影响，特别是考虑到下面讨论的索引优化。
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, * with main table's clustered index so you usually can't have it clustered in temporal order).
最糟糕的是,指数在嘘/审计表不需要100%相同的主要表,意味着你可以省略指数不需要查询审计数据从数据库嘘(因此加快插入审计表),反之亦然,优化指数有什么具体审计查询你已经通过时间戳(包括订购表通过聚集索引)没有主表背上那些缓慢的指标数据的变化(在集群上的更新,与主表的聚集索引发生冲突，因此通常不能按时间顺序聚集它)。
#2
6
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
http://en.wikipedia.org/wiki/Temporal_database
#3
1
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).
在这篇developerworks文章中讨论的历史表是一个包含数据库历史的表(即我们关于现实的信念的历史)。
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%.
注意区别。这两种观点的一致之处在于，我们过去对现实的看法确实是正确的。而这并不总是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.
如果使用前者是后者,那么你就在某种程度上假设,赞同度确实是100%,也就是说,所有你的过去有关现实的信仰总是伴随着现实定义,即假设不可能你对现实有任何错误的信念。
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.
保存其他表历史的表可以适合审计的目的。包含现实历史的表可以适合任何对历史信息感兴趣的用户。