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 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.
+1
A:
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?
Anax
2010-08-02 14:04:38
How then make prices like 4.99?
Mike Chaliy
2010-08-02 14:05:38
@Mike if VAT changes, businesses will re-calculate prices anyway to have "pretty" prices again.
Pekka
2010-08-02 14:08:02
I'm not sure I follow? You mean how to store the 4.99 in the database?
Anax
2010-08-02 14:08:24
The problem I see, we calculated price 4.99. Printed it for example on large boards. Then one day, VAT changed. I probably do not like idea to have all prices changed automatically.
Mike Chaliy
2010-08-02 14:11:46
No, he means how does a buisness calculate "pretty" price of 4.99, if DB stores ex-VAt. Easy, @Mike, What you store in the DB and what they user works with in the front send system do not have to be the same thing. I've done systems that let user manipulate either price (inc-vat or ex-vat) and we automatically calculate the other for them. Then we store ex-vat in the DB. (EDIT: Oh, Mikes problem is different, never mind)
James
2010-08-02 14:11:58
And also Anax is right, better to categorise products then store VAT rates just once per-category in a seperate table. Theres only 4 categories (if I recall correctly) so it's not hard.
James
2010-08-02 14:13:25
+2
A:
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.
ice cream
2010-08-02 14:05:31
While it's a 3NF violation to store base price, VAT and total price, it's usually simpler to denormalize and keep all three pieces of data. You only *need* two of the three and can always derive the third. But that's often a bunch of wasted multiplications since the sale is a historical **fact** and cannot easily be changed.
S.Lott
2010-08-02 14:08:37
@S.Lott: it gives me a dirty feeling to say this, but you make a pretty compelling argument that denormalizing is appropriate in this case.
rmeador
2010-08-02 15:35:53
@meador: That's the data warehousing approach: 3NF **only** applies if the data elements can be updated. Historical facts (like sales receipts) cannot ever be updated. Another receipt may reverse the transaction, but that receipt exists forever.
S.Lott
2010-08-02 15:55:17
A:
VAT in the UK has varied several times in the last year or so. I would keep Base Price separate from the variable VAT.
djna
2010-08-02 14:05:51
Naah, it's been 17.5% for at least a decade or so. Your basic point still stands though.
Pekka
2010-08-02 14:10:53
@Pekka - djna and Rob are both right. It WAS 17.5% for several years, then about a year and a half ago it was reduced to 15%. Now its back to 17.5% and it looks like it is going up to 20%.
Simon Knights
2010-08-02 15:02:38
A:
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.
Wes Price
2010-08-02 14:07:22
A:
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.
Bytecode Ninja
2010-08-02 14:10:17
Except if you're storing historical data, for example data that has been read in from an EPOS system. There's nothing to be gained by calculating the net or gross price at runtime when it can be stored. Plus the VAT rate applied to a sale is immutable once the sale is made.
Rob
2010-08-02 14:20:21
A:
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)
Rob
2010-08-02 14:14:00
A:
supercat
2010-08-02 15:19:06