Re: Order details table reference live data

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Fri, 01 Sep 2006 16:39:59 GMT
Message-ID: <znZJg.7000$9u.91608_at_ursa-nb00s0.nbnet.nb.ca>


Johan Sjöström wrote:

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

That is a very serious problem. After all, orders describe contracts. Thus, what the product was called at the time the order was made is important while what the product is called today is irrelevant. Ditto for the price etc.

This is not a question of normalization but a question of recording the proper information in the first place.

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

Needless.

> What are the recommended solutions for this type of situation?

Education. Clearly the folks who created the system lacked the necessary education to do create such a system. Sadly, such malpractice is ubiquitous in our industry.

  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.

Define minimal-impact. Does management prefer to minimize the future repercussions of a system that doesn't work? Or does management prefer to do nothing to fix the problem? Received on Fri Sep 01 2006 - 18:39:59 CEST

Original text of this message