Decimal Support in Rails

A couple of blog posts ago, I commented on the dangers of converting database decimal columns into Ruby floats. And, five months early, Santa delivers. In the Rails trunk, numeric and decimal database columns with a scale factor are now converted into Ruby BigDecimal objects. If the scale factor is zero, they instead become integers.

Migrations now support decimal columns too, with the addition of two new attributes, precision and scale.

   add_column :orders, :price,
              :decimal, :precision => 8, :scale => 2

I just spent a day reworking all the Depot chapters to use this, and it seems to work great. (And, no, the updated PDF isn’t released yet. I’m also half-way thought the ActiveRecord chapter rewrite, and need to get that finished before the next release.)

I’d be interested to hear[1] from folks currently using BigDecimal for financial calculations. Is there a preferred rounding mode? Any gotchas?

In the meantime, thanks core team.

1. dave∂pragprog.com

Comments

[John]

rails 1.2 has been released with support for decimal in migrations.

The documentation on what exactly scale and precision are is lacking. Does anyone know what they control?

I want to represent money in my database, but I don't know the best way.

Thanks

[Dave Thomas]

I talk about it in the book, but you can also look in any database resource. Precision is the total number of digits, and scale the number after the decimal point.

[Angus McDonald]

Dave,

My background is large-scale financial, specifically payroll, applications. We were lucky in that the Australian Tax Office specified exactly how we should round different components of an employee's pay, and unlucky in that they stopped at the conceptual level - which rarely helped us when doing intermediate calculations of amounts that would eventually be added into one (or more) components of their pay.

People get finicky about how much they're being paid, so even one cent either way was regarded as invalid. The result was that we spent a lot of time working out whether we needed to round to 2dp, 4dp or more. What was even weirder is that when calculating tax amounts you need to end up with whole dollars - and the rules for that rounding were different than for everything else.

So the gotcha for us was when you work out someone's pay as $15.56/hour * 7.6 hours * 120% loading, and then you want to know what 1/3 of that number is so you can calculate whether they triggered some archaic award condition ... then you can easily get confused as to when you round and how much you round by. For example, do you round the hourly rate times the number of hours before you apply the loading, or after, or both?