Archiving / Storing Historic Information

[Livid_Enema]

Hi there

I have read through a number of posts concerning storing derived fields and I am aware it is a controversial issue, so I wouldn't mind getting some opinions on my scenario.

I am designing a database which stores client and invoice information and creates printable invoices using reports. Each client has a certain VAT code assigned to them, which has a certain VAT rate e.g. Code S1 = 21%

Recently, the VAT rate in my country changed from 21.5% to 21% and could change again in future. How would you recommend reflecting this in a database? Clients will still have one VAT code (and hence VAT rate) attached to them, but the VAT rate can change. I need to hold the rate used at time of invoicing, so if I call up an historic invoice it will show the VAT rate charged at the time, not the new one which it could have been changed to since.

 

[KenHigg]

I think you need to replicate the current VAT rate at the invoice level.

 

[dfenton]

I believe that tax rates in general need some form of storage. I don't store the rate, but the result of the calculation in each invoice item. There are actually two reasons for this:

1. as you've found, tax rates change, so you can't link them to a tax rate table without complex logic (determining which rate applies for the particular period).

2. rounding is inherently problematic, so I think it's best to store the rounded tax amount so that the rounded value never has to be calculated again.

So, in a standard "David Fenton invoice", the invoice header stores the tax rate (either by name and % or by link to a record in a tax rates table), while each invoice item stores the calculated tax amount.

The same applies to a discount calculation, unless the discount can be different for each item on the invoice, in which case the rate is stored in the invoice (but not necessarily the discount amount, since it would be stored only for reason #2 above, not for reason #1, since the rate is stored in the record itself).

 

[gemma-the-husky]

This isnt so much an archiving problem, more of a normalisation problem

it will be pretty far-reaching, and may take a lot of effort to resolve

firstly you need to store a table or mechanism to store apprpriate rates of tax/VAT based on the applicable date (as you MAY have more than 1 possible rate at any point in time, because of your countries transitional arrangements)

secondly you need to modify your system to store the actual rate used - to avoid the
complex lookup issues referred to already