history of prices table

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

greets,


sybolt

 

[(PeteCresswell)]

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.

 

[John Spencer]

Although I prefer Mr Cresswell's first option. For several reasons - it is
history, you may have a special one-time discounted price for a customer, etc.

If you use option two, you need an effective date for the price (or an
effective date range). Then you would determine the price of an item based on
the purchase date.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Imda14u]

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.

Thanks Pete,

I suppose I would go for the second option.
Also because of what I understand from the first option (denormalization
is a new one for me) you would end up with extra history tables.

I'll give it a go