history of prices table

Per Imda14u:

Hi all,

In a table I store articles and their prices, on an order I create an
invoice.
Now, due to economic fluctuations we cannot buy some articles for the
same price.
What do I do to create a new price for the same article but don't want
to have old orders linked to the new price?

I suppose I need to create an extra table, with a one-to-many
relationship, like one article can have many prices?
How does a new order know which price to use?

Any help is greatly appriciated.

Two ways come to mind:

1) When the order is generated, prices are denormalized into
the order's line items. This is the one that rings true
to me bc the order is "history" and I'd want as little
likelihood as possible of history being changed.

"What you see is what you got."


2) - Each price record has an ID number. Call it "PriceID".

- Each line in the order number contains the PriceID of
the corresponding tblPrice record.

- When creating the order, the dropdowns for items/prices
are created by selecting only the currently-active prices.

I'd either have a column: tblPrice.IsActive and some logic
to ensure than only price for a given SKU is active at any
one time or select the most recent price for each SKU.

I'd guess that the hardcore DB architects would prefer this
one because it's more "database-like" and, technically,
saves space.
PeteCresswell