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

I don't see how a data mart solves this problem. The problem is in his operational database which doesn't track the historical details of the order. It's really got nothing to do with management. The customers of this company will drop their business once they've seen their unit price change automagically in front of their eyes. Received on Fri Sep 15 2006 - 22:19:27 CEST