designing history tables

Could some one please point me to a good resource on how to design history
tables in a dataware house situation'
For example, in the case of products table, if product description got
changed over time after the product was purchased. The old invoice still
shows the old description but the table reflects the new one.
How could some one keep track of both descriptions?
What would be a good design/architecture'
TIA...Well, the invoices table should contain a relatively permanent piece of
data, such as a ProductID of some kind, not a much more flexible piece of
information like a description.
That said, it may still be useful to store the history of a product.
Probably the simplest in this very specific case would be:
CREATE TABLE dbo.ProductDescriptionHistory
(
ProductID INT NOT NULL
FOREIGN KEY REFERENCES dbo.Products(ProductID),
Description VARCHAR(255),
EffectiveDate SMALLDATETIME
)
This will allow you to reconstruct invoices from the past, with the correct
"at the time" description, without bloating the invoices table with a big
VARCHAR that will usually be redundant.
You will probably come across the same dilemma with price... do you store
price data for products where the price may or may not change, or do you
just reference the productID?
Your exact solution will at least partially depend on some of the
information you haven't provided, such as exactly why you need the historic
descriptions, what you're going to do with them, and how often they actually
change.
"sqlster" <nospam@.nospam.com> wrote in message
news:3F41C612-B1AB-441F-AED7-26F4C7ABEA09@.microsoft.com...
> Could some one please point me to a good resource on how to design history
> tables in a dataware house situation'
> For example, in the case of products table, if product description got
> changed over time after the product was purchased. The old invoice still
> shows the old description but the table reflects the new one.
> How could some one keep track of both descriptions?
> What would be a good design/architecture'
> TIA...|||<Aaron>
Your exact solution will at least partially depend on some of the
information you haven't provided, such as exactly why you need the historic
descriptions, what you're going to do with them, and how often they actually
change.
</Aaron>
I need the historic descriptions for the reporting purposes only. Some of
the values could change 10 - 15 times a month.
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:

> Well, the invoices table should contain a relatively permanent piece of
> data, such as a ProductID of some kind, not a much more flexible piece of
> information like a description.
> That said, it may still be useful to store the history of a product.
> Probably the simplest in this very specific case would be:
> CREATE TABLE dbo.ProductDescriptionHistory
> (
> ProductID INT NOT NULL
> FOREIGN KEY REFERENCES dbo.Products(ProductID),
> Description VARCHAR(255),
> EffectiveDate SMALLDATETIME
> )
> This will allow you to reconstruct invoices from the past, with the correc
t
> "at the time" description, without bloating the invoices table with a big
> VARCHAR that will usually be redundant.
> You will probably come across the same dilemma with price... do you store
> price data for products where the price may or may not change, or do you
> just reference the productID?
> Your exact solution will at least partially depend on some of the
> information you haven't provided, such as exactly why you need the histori
c
> descriptions, what you're going to do with them, and how often they actual
ly
> change.
>
> "sqlster" <nospam@.nospam.com> wrote in message
> news:3F41C612-B1AB-441F-AED7-26F4C7ABEA09@.microsoft.com...
>
>|||Keep who changed the rows and when in separate columns in the archive table.
You might also want to keep a record of whether the row in the main table wa
s
updated or deleted.
E.g.:
Main table:
Col1 : Col2 : ... : ColN
Archive table:
Col1 : Col2 : ... : ColN : ChangedDateTime : ChangedBy : ChangeType
Of course changes are propagated to the archive table via triggers on the
main table (for update and for delete).
For a more elaborate solution, please at least provide DDL.
ML
http://milambda.blogspot.com/|||ML,
I am just looking for some books/websites/articles that address good history
table design. The example that I brought up is just a hypothetical example s
o
I don't have any DDL.
TIA..
"ML" wrote:

> Keep who changed the rows and when in separate columns in the archive tabl
e.
> You might also want to keep a record of whether the row in the main table
was
> updated or deleted.
> E.g.:
> Main table:
> Col1 : Col2 : ... : ColN
> Archive table:
> Col1 : Col2 : ... : ColN : ChangedDateTime : ChangedBy : ChangeType
> Of course changes are propagated to the archive table via triggers on the
> main table (for update and for delete).
> For a more elaborate solution, please at least provide DDL.
>
> ML
> --
> http://milambda.blogspot.com/