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... Quote:

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
End quote.

I recommend a data mart. Design the data mart as a star schema. Design dimension tables in such a way that
a change of a price of a product is stored by inserting a new row in the product dimension table. Use surrogate keys if necessary to accomplish this.

Build an ETL procedure that populates your star schema once a day. The hard part is excluding data previously loaded.

Integrate whatever OLAP tools you use with the star schema. Most good OLAP tools will recognize a star schema design, and with a little help can build a coherent multidimensional database from it. Once you get all of this stuff right, doing drill down analysis or preparing history reports will be as easy as playing a video game.

If you need to learn about data marts, data warehouses, and star schema design, read some of Ralph Kimball's books.

The above is based on my experience with a very similar web application for order processing, and on prior experience that I brought to that project.

I've summarized heavily in the above. Entire books have been written about the benefits and the pitfalls of copying data for historical purposes versus designing a single database suitable for both history and current operational use.

And, as is always the case with advice such as this, your mileage may vary. Received on Sat Sep 02 2006 - 13:55:57 CEST