Database Design Best Practice - Order processing database - Where updates to products could effectively change a closed invoice

Question

just after some insight on best practice here.

I'm building an order processing database, which manages stock, orders and invoices.

In my design so far, the invoices and orders have foreign key references to a products table. If the products were to be updated after the creation of an order / invoice, then the list of items on the rder / invoice could change if it were viewed again.

I'm planning to manage this by creating an "OrderItems" table, which stores an immutable copy of the product data as it was at the point where the order / invoice was created, and have the order reference this table rather than the changeable product table.

Is this a common method of handling this problem - is there another approach?

Thanks

Answer 1

This is related to the "Slowly Changing Dimension" problem in data warehouses. There are a half-dozen standard solutions.

http://en.wikipedia.org/wiki/Slowly_changing_dimension

Your "freeze a copy" is like the Type 4 algorithm.

Answer 2

Need to make a clear distinction between the price of a product, perhaps its default-price (in the Product table) and the price it was actually sold at (held in the InvoiceLine table ).