Skip to main content
D
Dax_Trajero
Inspiring
December 5, 2010
Answered

best practice for storing price of an item in database ?

  • December 5, 2010
  • 4 replies
  • 3452 views

In the UK we call sales tax, VAT, which is currently 17.5%

I store the ex-VAT price in the database

I store the current VAT rate for the UK as an application variable (VAT rate is set to change here in the UK in January)

Whenever the website display the price of an item (which includes VAT), it takes the ex-VAT price and adds the VAT dynamically.

I have a section in the website called 'Personal Shopper' which will happily search for goods in a fixed priced range eg. one link is under £20, another is £20-£50

This means my search query has to perform the VAT calculation for each item. Is this practice normal, or is it better to have a database column that stores the price including VAT ?

This topic has been closed for replies.
Correct answer Owainnorth

I'm also based in the UK, and this is what we do:

In our Products table, we store a Product Price excluding VAT and a VAT rate ID, which is joined off to a VAT Rates table. Therefore in order to calculate selling price yes, this is done at the SQL level when querying back data. To store the net, vat and gross amounts would be to effectively duplicate data, hence is evil. It also means that come January we only have to update that one row in one table, and the whole site is fixed.

However.

When someone places an order, we store the product id, net amount, vat code id, vat amount and vat percentage. That way there's never any issue with changing VAT codes in your VAT codes table, as that'll only affect live prices being shown on your website. For ever more whenever pulling back old order data you have the net amount, vat amount and vat percentage all hard-coded in your orders line to avoid any confusion.

I've even seen TAS Books get confused after a VAT change where in some places on an order it recalculates from live data and in others displays stored data, and there have been discrepancies.

I've seen many people have issues with tax changes before, and as database space is so cheap I'd always just store it against an order as a point-in-time snapshot.

O.

4 replies

Big Mad Kev
Big Mad Kev
Participating Frequently
December 6, 2010

or another for the search is to display to the user the inc vat value, and the search actually uses the non-vat value. Then you don't need to work it out on the fly.

But I'd go with the above duplicated solution

D
Dax_TrajeroAuthor
Inspiring
December 7, 2010

Thanks Kev

60 percent of the time, it works every time.

D
Community Expert
Dave WattsCommunity Expert
Community Expert
December 5, 2010

You should store the regular price and calculate VAT at run-time for new orders. When recording the order itself, you should store the current value including VAT, as it may change in the future.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on

GSA Schedule, and provides the highest caliber vendor-authorized

instruction at our training centers, online, or onsite.

Read this before you post:

http://forums.adobe.com/thread/607238

Dave Watts, Eidolon LLC
Owainnorth
Owainnorth
Inspiring
December 5, 2010

Again, exactly the same as my reply but thanks anyway Dave.

A
Adam Cameron.
Inspiring
December 5, 2010

I somehow suspect Dave doesn't bother reading the actual thread, he just reacts to whatever is in front of him in his email inbox.

Still: I guess having two people responding in the same fashion is affirmation that the suggestion is a valid one.

--

Adam

D
Dan_Bracuk
Inspiring
December 5, 2010

I would store it in the database, complete with effective dates.

Owainnorth
OwainnorthCorrect answer
Inspiring
December 5, 2010

I'm also based in the UK, and this is what we do:

In our Products table, we store a Product Price excluding VAT and a VAT rate ID, which is joined off to a VAT Rates table. Therefore in order to calculate selling price yes, this is done at the SQL level when querying back data. To store the net, vat and gross amounts would be to effectively duplicate data, hence is evil. It also means that come January we only have to update that one row in one table, and the whole site is fixed.

However.

When someone places an order, we store the product id, net amount, vat code id, vat amount and vat percentage. That way there's never any issue with changing VAT codes in your VAT codes table, as that'll only affect live prices being shown on your website. For ever more whenever pulling back old order data you have the net amount, vat amount and vat percentage all hard-coded in your orders line to avoid any confusion.

I've even seen TAS Books get confused after a VAT change where in some places on an order it recalculates from live data and in others displays stored data, and there have been discrepancies.

I've seen many people have issues with tax changes before, and as database space is so cheap I'd always just store it against an order as a point-in-time snapshot.

O.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices