To save prices with or without VAT?

I am curious what the best practice is. For example we have product entity, it has two fields: Price and VAT. What to save in Price开发者_Go百科 value? Base price, and then calculate result price based on base price and VAT code. Or save calculated price and save VAT just for information purposes.

Without VAT, since it can change independently from prices.

Edit: by the way, why are you storing the VAT for each product? Isn't it better to categorize your products (if you have different types of VAT) instead?

Since VAT can change, I recommend storing the base price and the VAT percentage at the time of the sale. Then you can display the calculated price and the VAT percentage depending on what you need to report on.

Aside: The standard rate of VAT in the UK is due to change at the beginning of January 2011 from 17.5% to 20%, any solution should handle this kind of change.

The solution I've used previously is to have the following:

Product:
NetPrice (MONEY, NOT NULL)
VATRateId (INT, NOT NULL, FK -> VATRate.VATRateID)

VATRate
VATRateId (INT, PK NOT NULL)
Description (TEXT NOT NULL)

VATRateValue
VATRateValueId (INT, PK NOT NULL)
VATRate (MONEY NOT NULL)
EffectiveToDate (DATETIME NULLABLE)

That way I can store that Product X has a net price of 1.00, with a VAT Rate of {1, Standard Rate VAT}, which will apply the following rates { 17.5% until 2010/12/31, 20% thereafter}

The one thing this solution doesn't cater for is you changing the price of the product to ensure that, irrespective of the current VAT rate, the price always remaining at a certain "price-point" such as 4.99. What you could do here, for maxium flexibility (with increased complexity) is move the NetPrice field from the Product entity to a ProductPrice entity:

ProductPrice
ProductPriceId (INT, PK NOT NULL)
ProductId (INT, NOT NULL, FK -> Product.ProductId)
Price (MONEY, NOT NULL)
EffectiveToDate (DATETIME NULLABLE)

VAT in the UK has varied several times in the last year or so. I would keep Base Price separate from the variable VAT.

Product prices are best saved without VAT as already mentioned VAT rate can change independantly of prices, many of the databases I work on have the VAT rate(s) stored in a separate table, the price + vat is then calculated by picking a VAT rate from the VAT table.

Changes are easier to implement this way too, such as if the VAT rate changed from 17.5% to 20% you only have to change one row to have all your prices updated accordingly, rather than change every individual price.

If you store price + VAT, your database's integrity can be comprised if you update the VAT and forget to update the price + VAT. This won't occur if you store the raw price. In short, it is better not to store values that can be obtained by a calculation over the columns of a row.

In situations where there are three values to be stored in a database, such that knowing any two one can compute the third, I sometimes favor storing all three values along with an indicator of which two are "real" and which one is computed. The three values should always be equal; if they're not, one should examine what's going on and ascertain why.

For example, it may be useful to store timestamps as time zone, UTC, and local time, along with a "what is known" indicator. For example, if some time stamps are found to have been recorded using the wrong time zone, the "what is known" indicator can be used to determine whether the UTC or local time should be adjusted.

With regard to prospective rather than historical pricing information, I would think it might be helpful to store VAT-exclusive price, expected VAT, and VAT-inclusive price, along with a mode flag indicating various scenarios, such as

1. The VAT-inclusive price (VIP) should precisely track the VEP+VAT, to the nearest pence
2. The VAT-inclusive price (VIP) should precisely track the VEP+VAT, to the nearest 5p
3. The VAT-inclusive price (VIP) should precisely track the VEP+VAT, to the nearest 20p-1
4. The VAT-inclusive price (VIP) should precisely track the VEP+VAT, to the nearest 50p-1
5. The VAT-inclusive price (VIP) should precisely track the VEP+VAT, to the nearest 100p-1
6. The VAT-inclusive price should remain fixed if the VAT changes, but ip updating the VAT causes the VEP+VAT to exceed the VIP, the record should get flagged to suggest that someone consider increasing the VIP.

Basically, figure out what should happen if the VAT changes and adjust things accordingly.