How to handle money and currency in web applications

May 28, 2014

Table of contents:

1. Use integers not floats
2. Decide how you want to round awkward values
3. Store the currency type and exchange rate
4. Keep tax and the tax rate separate
5. Keep discounts separate
6. Conclusion

Ecommerce websites are one of the most common varieties of web applications on the modern Internet. Companies all over the world are looking for ways to connect with their customers and expand their businesses.

In 2014 there are many options for building an ecommerce website. Services like Shopify offer a completely hosted experience, whilst Open Source projects like Magento give you more control over the design, experience and integration of your web site into your company.

However, it is often the case that even an Open Source project like Magento still does not meet your requirements. I’m all for not reinventing the wheel, but sometimes you just can’t fit a square peg in a round hole. This is where developing a bespoke ecommerce application is really beneficial.

One of the stumbling blocks to building an ecommerce application is the problem of how to store financial data. Dealing with money is definitely not something you want to mess up as it could end up with big implications for your company.

In this article I’m going to show you the common misconceptions, pitfals and problems that occur when trying to store monetary values in a database. If you are looking to build your own ecommerce software, hopefully this will be a good place for you to start.

Use integers not floats

The first important rule is, always store values as fractional units.

It can be tempting to store a value such as $10 as 10.00 in your database. However, storing monetary values with a decimal point can cause a lot of issues. Instead, you should always store monetary values in their minor unit form. So to record $10, you would save it as an integer in your database as 1000.

Why is it better to store monetary values as minor units? Well, floating point arithmetic can cause rounding errors.

The problem stems from the fact that floats and doubles cannot accurately represent the base 10 multiples that we use for money. I’ll not get into the technical reasons why this is the case as I’m sure someone on the internet has already explained it better than I ever could.

If your application is only doing really basic arithmetic on your money values you might be able to get away with it, but for every calculation that is ran, the level of precision will decrease. This means you will likely find 1 penny errors creeping in to your application.

Decide how you want to round awkward values

If your application will be offering discounts, you will very quickly hit a point when discounting a value will leave you with an awkward total.

For example, if you are selling something for $100 and you put a 1/3 discount on it, the value of the discount will be 33.333333333333336.

You can either decide to round up to $33.34 or down to $33.33. This choice is really up to you, but you need to decide a system for rounding numbers and make it consistent.

Store the currency type and exchange rate

If you are selling physical products through your application, it’s likely that you will only be selling to domestic customers for the foreseeable future. However if you are selling digital products and you plan to accept different currencies, you need to correctly record some important bits of transactional data.

Firstly, for each transaction you should record the value (e.g 1000), the currency type (e.g USD) and the exchange rate at that moment in time (e.g 1.234).

It’s extremely important that you record these bits of data for each transaction so you can normalise your sales data to give accurate profit and loss totals. Recording a transaction in a different currency, but without noting the currency type and the exchange rate will destroy any type of accounting system that you try to use to reconcile your accounts.

Keep tax and the tax rate separate

Each different country has their own tax rules and regulations. In some countries you will have to charge 10% tax, whilst in others you will have to charge 20% tax.

As with the currency type and the exchange rate, you should also keep a record of the amount of tax and the rate for each transaction. Storing a $10 transaction with a $1 tax as 1100 in your database will be meaningless when you come to calculate your end of year tax returns and how much profit your company created in the financial year.

Keep discounts separate

If you are offering discounts on your products, you should record the full value of the transaction and then record the discount separately to get an accurate value of the transaction.

For example, if you were offering a 30% discount on a sale of $100, you would record the sale as 10000 and the discount as 3333. Now when you come to reconcile your sales you can simple get the correct value of the sale by subtracting the discount from the total.

Conclusion

Creating an ecommerce website could be a very profitable new venture, or it could turn into an living legal nightmare. At the outset, building an ecommerce website seems pretty easy, but you will quickly find yourself in a sticky situation when it comes to dealing with your accounts.

In this article I’ve covered some of the common pitfalls when building an ecommerce website, but I haven’t really given you any solutions.

Over the next couple of weeks I will be looking at building some PHP packages that can prevent these problems from ever occurring. If you are looking to build an ecommerce website, subscribe to my newsletter so you don’t miss an article!