Order details table reference live data

From: Johan Sjöström <daddynahoj_at_hotmail.com>
Date: 1 Sep 2006 08:43:58 -0700
Message-ID: <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.

Johan Sjöström
MSc, MCAD Received on Fri Sep 01 2006 - 17:43:58 CEST

Original text of this message