increasing prices

[Sandeman]

Hello,

I have this problem with changing prices in tables. I have a table
tblInvoices and a table tblPrices
tblInvoices
nr. | date | product
-------------------------
001 | 1-1-03 | A
002 | 1-2-03 | B
........


tblPrices
product | price
---------------
A | $ 100,-
B | $ 200,-

Now on 1-3-03 i decide to rease the price of product A with 10% --> $ 110,-.
When i do, the prices of het invoices before 1-3-03 change with it. All
history invoices with product A become more expensive.

What is the most common thing to solve this problem? Please give me some
SQL, some table information. I can't find it anywhere.

Thanks in advance

Sandeman

 

[Joe Fallon]

The most common way to handle this is to store the price as part of each
transaction.
You may think you are breaking relational design techniques by storing the
same piece of data twice, but you are not.
This is a well known "exception" if you will.
When data is time sensitive, it is best to store the value at that moment in
time rather than refer back to the Price table which causes the problem you
have described.

A second solution is to add start and end dates for the effective price but
that adds a lot of complexity to your queries because you need to know the
date of the transaction in order to lookup the right price. I built an appo
this way once and it worked, but I much prefer the first solution now.