Best approach to views on archive data with change logs

Question

(Sorry about the vagueness of the title; I can't think how to really say what I'm looking for without writing a book.)

So in our app, we allow users to change key pieces of data. I'm keeping records of who changed what when in a log schema, but now the problem presents itself: how do I best represent that data in a view for reporting?

An example will help: a customer's data (say, billing address) changed on 4/4/09. Let's say that today, 10/19/09, I want to see all of their 2009 orders, before and after the change. I also want each order to display the billing address that was current as of the date of the order.

So I have 4 tables:

Orders (with order data) Customers (with current customer data) CustomerOrders (linking the two) CustomerChange (which holds the date of the change, who made the change (employee id), what the old billing address was, and what they changed it to)

How do I best structure a view to be used by reporting so that the proper address is returned? Or am I better served by creating a reporting database and denormalizing the data there, which is what the reports group is requesting?

Answer 1

There is no need for a separate DB if this is the only thing you are going to do. You could just create a de-normalized table/cube...and populate and retrieve from it. If your data is voluminous apply proper indexes on this table.

Answer 2

Personally I would design this so you don't need the change table for the report. It is a bad practice to store an order without all the data as of the date of the order stored in a table. You lookup the address from the address table and store it with the order (same for partnumbers and company names and anything that changes over time.) You never get information on an order by joining to customer, address, part numbers, price tables etc.

Audit tables are more for fixing bad changes or looking up who made them than for reporting.