I’ve got an e-commerce shop running on mysql 5.0.45.
Prices are stored in the database without tax (now 17.5%). Tax was 15%. The client painstakingly went through and rounded all of their prices so that when tax was at 15%, you’d get a nice round figure eg £10.50, not £10.48. What I want to do is adjust the prices in the database based on the current prices, so that the taxed price is nicely rounded.
As I see it happening:
Take current non-taxed price
Add tax (multiply by 1.175)
Round value up to nearest £0.10
Deduct tax from rounded value (divide by 1.175)
UPDATE the value
Does this make sense? How can I tackle the rounding?
What you might find useful is rather than just do the update, do a SELECT and see what your values for current price, new price, new price with VAT will look like.
What I was trying to get back to was a new price with only two decimal digits. Since you are storing real numbers what you want to avoid happening is a price of 10.21 being recorded as 10.2134567. Somebody then buys three items and the system charges them an extra penny.
I guess there is always going to be a problem with rounding, whatever you do. I have 2 price fields (both ex VAT). One for offer prices and one for RRP. If I apply the rounding to the RRP, my offer price could be problematic and vice-versa. I guess the RRP would be the more sensible one to apply the rounding to as it is the ‘normal’ price so will have less chance of changing.
If you store the price in pence would that avoid the problem. We all tend of it as a real number because of the way it is presented but it is really an integer.
Aren’t RRP prices defined by the supplier and you would invalidate that if you rounded it.
I guess it depends on what you are selling and who you are selling to but isn’t the price with VAT what most customer expect to see. The underlying VAT calculation is not really of interest and is really there only for you and Customs and Excise.
I agree about the way the price is stored could make sense to store as an integer but I’m a believer that your data should look like what it is without the need to manipulate it afterwards.
RRP is just the field name in the shop. In general we use price1, but by putting in the RRP, it triggers the ‘sale’ code which works out the savings to the customer.
Prices are stored without tax as some shipping zones aren’t taxed.
create table tax
( rate DECIMAL not null
, date_from DATE
, date_to DATE
)
In my own db, I have the prices in their own columns and the applicable tax is retrieved from the db when the price is to be displayed. This means that if the tax rate changes, you only have to update the tax table with the new rate and the new start date - setting the end date of the previous tax to the last date when it was applicable.
explanation:
I think you are in the UK so you may have noticed how some supermarkets had wierd prices in 2009 during the temporary 15% period. no longer ending in .99 or .49 for example, the prices ended sometimes in .23 and the shops explained this as a way of showing they hadn’t taken advantage of the tax change to milk their customers. No rounding was needed, except the nearest penny).
Previous base price took account of the 17.5% so their end prices appeared normal with the 17.5% rate which of course they have now reverted to. If, as anticipated, the tax increases long term, post election, the base price would be better changed at that stage but not for a temporary levy.
Might save a lot of time if you reset the pricing to the base price and add that extra table. And I don’t think it is a wise expense to undertake this price changing exercise for a third and fourth time.
I know that covers more than you asked but it may be of use as a way to manage time (therefore competitiveness), better
I would also suggest considering showing a discount rate rather than having two columns each with a price. (RRP and discounted).
That would mean you can show a prpice using
RRP + vat_rate - discount rate
or
RRP - discount_rate + vat rate.
depending on whether the discount is to come off the RRP or the full price.
I think that would be easier to manage long term and give more accurate figures to the customer. preventing 12.48172354 sort of prices can be done in your langauage of choice to make it 12.49 (rounding to the neartest penny).