Re: Order details table reference live data

"Johan Sjöström" <daddynahoj_at_hotmail.com> wrote in message news:1157125438.678565.5920_at_b28g2000cwb.googlegroups.com... This is a real pickle. My company's web application offers a lot of ways to order products, so there are quite a few Order/OrderDetail tables in the db (one for every type of order).

The problem is that the Order Detail tables aren't denormalized, instead they reference current live data such as the Product table. Alas, when someone changes the name or price of a product that has already been ordered, that order is affected.

Needless to say, this means that all orders and history reports can get really screwed up.

What are the recommended solutions for this type of situation? A lot of tables and a lot of foreign keys are involved. I don't have any OLAP experience. Creating history tables for basically every table in the system seems like a nightmare also. The db has approx. 1200 stored procedures and 180 tables. Minimal-impact solutions are, as always, preferred by the management.

Cheers,
Johan Sjöström
MSc, MCAD Received on Sat Sep 02 2006 - 13:41:04 CEST