designing history tables

5 Jan 2006 10:41 PM

sqlster

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

5 Jan 2006 10:51 PM

Aaron Bertrand [SQL Server MVP]

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.

Show quote

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

6 Jan 2006 12:38 AM

sqlster

<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

Show quote

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

6 Jan 2006 12:59 AM

ML

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

6 Jan 2006 1:52 AM

sqlster

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 so
I don't have any DDL.

TIA..

Show quote

"ML" wrote:

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