History Tables: Always Subtables?

[Neil]

When creating history tables that are appended to whenever a record is
updated, should one append the corresponding child table records to their
history tables at the same time (so as to have a complete set for each
edit); or should one append the child tables only when those particular
tables are edited? I have been doing the latter, but I thought I'd ask for
others' opinions.

Thanks!

Neil

[Erland Sommarskog]

Re: History Tables: Always Subtables?

Neil (nospam@nospam. net) writes:

When creating history tables that are appended to whenever a record is
updated, should one append the corresponding child table records to their
history tables at the same time (so as to have a complete set for each
edit); or should one append the child tables only when those particular
tables are edited? I have been doing the latter, but I thought I'd ask for
others' opinions.

I would say this depends on business requirements and how you actually
uses these tables. Sometimes it can be comfortable to know that you
always read the historic table even if by chance you want the current
data. For instance "Show me the version of the contract that was valid
on Aug 7th". If the history table does not have the current version, you
will need to look in two tables. And if you fail to do that, you will get
the wrong version...

But if data is such that always save a row every day, there may be less
need to have the current data in the history. Particularly if it can
change several times during the day, and you are only saving a nightly
snapshot.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx