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/

Design question SQL Server 2005

I am very new to SQL Server 2005. I have a database of criminal records consisting of a master table of names where the primary key is a case number and 3 related minor tables: violations, charges, and appeals. They are linked by the case number with a one-to-many relationship. Not all of these tables have a record for every master record.

I am designing a simple lookup program in VB 2005 in which info from all tables will be on one screen. A search will be done on either name or case number.

What is the best way so set up views and/or stored procs for such a program? Do I need a stored proc for each minor table or is there a way to set up one proc to pull all of the info? Did I mention I was new at this? I have worked a lot with Access and recognize just enough things in SQL Server to be really confused.

Thanks for the help!

To get you started, you can get all the info with a single query. eg if we assume case number:

SELECT m.col1, v.col1, c.col1, a.col1
FROM masterTable
LEFT JOIN violations v
ON m.caseid = v.caseid
LEFT JOIN charges c
ON m.caseid = c.caseid
LEFT JOIN appeals a
ON m.caseid = a.caseid
WHERE m.caseid = @.caseid

Depending on how your app should work, you may not want to display all details if name is serached for, but rather use name as a way to look up the case number first, then go query for the details.

/Kenneth