Preserving old records states

The relational db advantage that is after normalization data is not
duplicated like in old flat-file days. But I am trying to understand how
I would design tables to preserve old record states. An example will
better illustrate my question.

Say you invoice table and items can have 3 tax-types merchandise, food,
and tax-exempt. So you have invoice table with a foreign key to a tax
table with description and rates, 5%, 2%, 0% respectively. So the state
legislature changes the rates effect July 1st to 6%, and lowers food to
1%. Change the tax table and great, all orders now will have the latest
rates, but if you look up an older invoice before the rate change then
those records will be incorrect. How best to design tables such that it
will preserve old records without resorting to old methods; the PPC
method (Print Paper Copy ;-) ), or flat-file method where you just
duplicate the tax type and rates in the one table?

--
Take care,

Jonathan
-------------------
LITTLE WORKS STUDIO
http://www.LittleWorksStudio.com

Jonathan N. Little wrote:
> The relational db advantage that is after normalization data is not
> duplicated like in old flat-file days. But I am trying to understand how
> I would design tables to preserve old record states. An example will
> better illustrate my question.
>
> Say you invoice table and items can have 3 tax-types merchandise, food,
> and tax-exempt. So you have invoice table with a foreign key to a tax
> table with description and rates, 5%, 2%, 0% respectively. So the state
> legislature changes the rates effect July 1st to 6%, and lowers food to
> 1%. Change the tax table and great, all orders now will have the latest
> rates, but if you look up an older invoice before the rate change then
> those records will be incorrect. How best to design tables such that it
> will preserve old records without resorting to old methods; the PPC
> method (Print Paper Copy ;-) ), or flat-file method where you just
> duplicate the tax type and rates in the one table?
>

Store the tax rate (and other things which may change - like the price
of the item(s)) in the invoice.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:
> Jonathan N. Little wrote:
>> The relational db advantage that is after normalization data is not
>> duplicated like in old flat-file days. But I am trying to understand
>> how I would design tables to preserve old record states. An example
>> will better illustrate my question.
>>
>> Say you invoice table and items can have 3 tax-types merchandise,
>> food, and tax-exempt. So you have invoice table with a foreign key to
>> a tax table with description and rates, 5%, 2%, 0% respectively. So
>> the state legislature changes the rates effect July 1st to 6%, and
>> lowers food to 1%. Change the tax table and great, all orders now will
>> have the latest rates, but if you look up an older invoice before the
>> rate change then those records will be incorrect. How best to design
>> tables such that it will preserve old records without resorting to old
>> methods; the PPC method (Print Paper Copy ;-) ), or flat-file method
>> where you just duplicate the tax type and rates in the one table?
>>
>
> Store the tax rate (and other things which may change - like the price
> of the item(s)) in the invoice.
>

I would concur with Jerry here. Anytime you have quotes, invoices,
orders, etc. and you have prices, taxes, etc that are calculated and
sent to customers, they should be stored in the invoice, orders or
quotes table and NOT recalculated every time you display them. If
someone orders something, and there is a price increase prior to the
invoice, you have already said you would give it to them at $x even
though the price is now $y. An order is an implicit contract and should
be honored at the quoted price - even if the price increase goes into
effect 1 minute after the order is complete. The invoice is the explicit
contract for the order.

So you want to store EVERY detail of the order and the invoice is
generally generated from the order - and just to make things more
confusing, if you must, you can use your "invoice" as the "order" - but
again, store ALL details in this table.

For example: if you quote someone something and the quote is good for 60
days. The quote would of course state "good for n days - taxes,shipping
not included" and you would have to honor this price for that period -
even if you had price increases.