To save prices with or without VAT?

Question

I am curious about what is the best practice. For example, we have a product object, it has two fields: Price and VAT. What to save in price? The base price, and then calculate the price of the result based on the base price and VAT code. Or save the estimated price and save VAT for informational purposes only.

Answer 1

Without VAT, since it can change regardless of prices.

Edit : By the way, why do you keep VAT for each product? Isn't it better to classify your products (if you have different types of VAT)?

Answer 2

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

Answer 3

In addition : the standard VAT rate in the UK should change in early January 2011 from 17.5% to 20%, any decision should handle such changes.

The solution I used earlier is this:

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

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

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

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

The only thing this solution is not suitable for is the change in the price of the product to ensure that, regardless of the current VAT rate, the price always remains at a certain "price", for example, 4.99. What you can do here, for maximum flexibility (with increased complexity), move the NetPrice field from the Product object to the ProductPrice object:

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

Answer 4

UK VAT has changed several times in the last year or so. I would keep the base price separate from variable VAT.

Answer 5

Prices for goods are best preserved without VAT, since the already mentioned VAT rate can vary regardless of prices, many of the databases I work on have a VAT rate stored in a separate table, then the price + wat is calculated by choosing the VAT rate from the table VAT.

Changes are also easier to implement in this way, for example, if the VAT rate has changed from 17.5% to 20%, you only need to change one line so that all your prices are updated accordingly, rather than changing each individual price.

Answer 6

If you store the price + VAT, the integrity of your database can be included if you update the VAT and forget to update the price + VAT. This will not happen if you save the raw price. In short, it’s best not to store values ​​that can be obtained by calculating over the columns of a row.

Answer 7

In situations where three values ​​are stored in the database, so that knowledge of any two can calculate the third, I sometimes prefer to store all three values ​​together with an indicator, of which two are "real" and which is calculated. Three values ​​must always be equal; if this is not the case, you should study what happens and find out why.

For example, it may be useful to store timestamps as a time zone, UTC, and local time along with an “what is known” indicator. For example, if it is found that some timestamps were recorded using the wrong time zone, the “what is known” indicator can be used to determine if UTC or local time should be adjusted.

Regarding potential rather than historical pricing information, I would think that it would be useful to store the value of exclusive VAT, the expected VAT and the price including VAT, as well as a mode flag indicating various scenarios, such as

• Price including VAT (VIP) must accurately track the VEP + VAT, to the nearest pence
• Price including VAT (VIP) must accurately track VEP + VAT to the nearest 5p
• VAT Price (VIP) must accurately track VEP + VAT to the nearest 20p-1
• Price including VAT (VIP) must accurately track VEP + VAT to the nearest 50p-1
• Price including VAT (VIP) must accurately track VEP + VAT to the nearest 100p-1
• The price including VAT should remain fixed if the VAT changes, but the IP-update of the VAT leads to the fact that the VEP + VAT exceeds the VIP, the record should be marked to suggest that someone will think about increasing the VIP.

In principle, find out what should happen if the VAT changes and is adjusted accordingly.