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). SQL Server 2000 is used
at the moment.

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

(Accidentally posted this in the wrong newsgroup first in case someone
noticed)

On 1 Sep 2006 08:52:32 -0700, Johan Sjöström wrote:

Show quoteHide quoteHi Johan,

Usually, products have two types of attributes: those that are supposed
to stay the same over time (such as name, color, materials used - if one
or more of those change, you've actually created a new product and
should treat it as such), and those that are expected to change (price
is a very typical example).

Standard design would be to have the "non-changing" attributes in a
single table, with ProductNumber (or whatever your business uses to
identify products) as primary key, and the "changing" attributes in one
or more tables with compound keys - like this one:

CREATE TABLE dbo.PriceList
       (ProductNo int NOT NULL,
        ValidFrom datetime NOT NULL,
        ValidTo datetime DEFAULT NULL,   -- NULL means still valid
        Price decimal(8,2) NOT NULL,
        PRIMARY KEY (ProductNo, DateFrom),
        UNIQUE (ProductNo, DateTo),
        FOREIGN KEY (ProductNo) REFERENCES dbo.Products(ProductNo)
            ON UPDATE CASCADE ON DELETE CASCADE,
        CHECK (VallidTo > ValidFrom)
       )
-- You also need triggers to check that there are no overlaps
-- or gaps between pricelist entries for the same product.
-- Unfortunately, SQL Server can't do this with constraints.

If a price (or other "changing" attriibute) changes, you set the ValidTo
date in the row with ValidTo IS NULL and add a new row for the new
prices. When checking existing orders, you use
  FROM Orders AS o
  JOIN PriceList AS p
    ON o.OrderDate BETWEEN p.ValidFrom
                       AND COALESCE(p.ValidTo, o.OrderDate)

If a "non-changing" attribute has to change, it's either a correction of
a data entry error (in which case yoou *WANT* existing orders to be
affected as well), or you have actualy replaced the product with a new
one and should treat it as such - i.e. remove the existing product from
your list (by setting a "DiscontinuedDate" column rather than deleting
the row, because the latter would ruin existing orders), and enter the
renewed product as a new one, with a new product number, ID, or
whatever.

History tables are usually of the form:

CREATE TABLE PriceHistory
(upc CHAR(13) NOT NULL
  REFERENCES Inventory(upc),
start_date DATE NOT NULL,
end_date DATE, -- null means current
PRIMARY KEY (upc CHAR(13) NOT NULL,
item_price DECIMAL (12,4) NOT NULL
  CHECK (item_price > 0.0000),
CHECK(start_date < end_date),
etc.);

You then use a BETWEEN predicate to get the appropriate price.

SELECT ..
  FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
           AND COALESCE (end_date, CURRENT_TIMESTAMP);

It is also a good idea to have a VIEW with the current data:

CREATE VIEW CurrentPrices (..)
AS
SELECT ..
  FROM PriceHistory
WHERE end_date IS NULL;

UPDATE in a SQL script file is not working.
Problem with WHERE clause
SET/SELECT truncate string
parse out set of numbers from a column
If Else Statement