Re: Order details table reference live data

Johan Sjöström schrieb:
> 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.

Once we need to access historic records, it does make sense to switch to   a kind of version management system where some entities have versions. If an order has a date then this date can be used to select the necessary version of other data items such as product (price, which depends on time), or customer (address, which changes in time). If no version is specified then normally the latest record is retrieved.

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

OLAP can help here but not very much because it is intended for other purposes (but it has operations that can be useful for version management). It is not necessary to implement full-featured version management - you can gradually introduce its elements in design. The simplest (and the worst) form would be to copy the current state of the data in a separate location. For example, to store somewhere the current product price and the current customer address. Yet, it is rather bad solution because this state is not easily available for other elements of the model. Say, it is difficult to produce historic reports.

> Cheers,
> Johan Sjöström
> MSc, MCAD
>

--
http://conceptoriented.com