sql server - Best approach to views on archive data with change logs -

(Sorry about the opacity of the title; I do not think actually to say what I see without writing I'm a book.)

Then in our app, we allow users to change the major pieces of data. I keep records of records that when a log is changed into schema, but now the problem presents itself: How do I represent best in a scenario for the report?

An example would be helpful: Data of a customer (say, billing address) was changed on 4/4/09 Let's assume that today, 10/19/09, all of my 2009 orders Want to see, before and after change I also want that every order will be displayed to show the existing billing address as per the order date.

Therefore, I have 4 tables:

Order (with order data) Customer (customer customer (connecting two) Customer changes (which keeps date of change, Employee ID), what was the old billing address, and how he changed it)

How do I use the best one view through reporting to return the address properly? By creating a better reporting database and there What are the Report Groups requesting?

A separate DB There is no need for it if this is the only thing you are going to do that you can just make a D-generalized table / cube ... and fill it with and recover. If your data applies to the appropriate index on this table