Re: VAT rate or VAT amount as a column?

Emily Jones wrote:

> Customers are charged for a service (a training course) and at the moment
> always plus VAT.
>
> At the moment I've got, something like:
>
> CustomerCourse:
> ID, name, ... Fee, Vat
>
> where vat is an amount calculated using the current rate. I'm not storing
> the total, as that's derived.
>
> Maybe I should store the VAT as a %, because the amount is really
> derived. And if I've got the capability to have a different VAT rate for
> each CustomerCourse record, then there's the flexibility that I might
> need, if, for instance there is a reason to charge a particular customer a
> different rate of VAT.
>
> Opinions please.
>
> Emily Jones

Assuming VAT can be different over time or by location, you want a table of VAT codes and tables. You put the VAT code and percentage into the table, and then put a code onto each customer/sale row. You then can refer to the table for the percentages.

The above system is considered normalized, it will prevent a lot of mistakes and give some auditing abilities.

Personally, I then have the percentage "fetched" into the services table and the amount calculated and stored (so to answer your question: both). This is done with a trigger that will not allow users to overwrite these calculated values. This builds a nice audit trail into the table.

-- 
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)