Mapping to BigDecimals instead of Floats

Hi everyone,

I’m new to Rails and Ruby, and have been working my way through the
Pickaxe
and Agile Rails books for the last week or so. With a background in
payroll
apps I found that the default ActiveRecord mapping of decimal columns to
float variables really bothered me! Financial calcs need high levels of
accuracy and floats just don’t give you that.

I know there are a bunch of people working on fixing that, but I also
realise that with the added complexity of needing to handle different
DBs
the change may take a long time (if it ever comes).

In the meantime I have worked out how to override the accessor/mutator
in my
model classes to avoid this issue. Now I am sure that most of you
already
know how to do this, but I couldn’t find any clear examples of exactly
what
I wanted.

So once I worked it out for myself, I posted the solution code on my
bloghttp://falkayn.blogspot.com/2006/04/playing-with-ruby-on-rails.html.
I really hope this helps someone else out there because it caused far
too
much pain for me to want to have gone through it just as a personal
exercise.

Cheers!

P.S. For those of you viewing this as text, the blog entry is at:

I’m not clear why everyone wants BigDecimal support.

Do you need larger numbers than the Money gem can
handle?

Money give you the accuracy by storing as integer,
and gives you a head start on multi-currency as well,
and integrates with Rails perfectly via composed_of.


– Tom M.

Tom,

Personally it’s because I want my database schema to represent reality,
and
in the case of payroll applications (in Ausralia at least) hourly rate
is
typically stored to 4dp, yet it is a currency field. It is also the way
legacy databases are often setup.

Perhaps I am too old-school, but IMO there are some very clear instances
when performance or architecture considerations mean that the database
needs
to include business rules, and so stored procedures are required. In
those
cases I would rather be dealing with a true decimal data type than
mucking
about with integers, or some user-defined data type.

Also, I had a look at the Money type and I have yet to find a clear
description of exactly how it will help me. The existing docs seem to be
written for API developers, which I am not, and so are a little hard for
me
to understand.

Cheers,
Angus

On Apr 9, 2006, at 4:56 PM, Angus McDonald wrote:

Personally it’s because I want my database schema to represent
reality, and in the case of payroll applications (in Ausralia at
least) hourly rate is typically stored to 4dp, yet it is a currency
field. It is also the way legacy databases are often setup.

Ah, that’s interesting. Is it typical in Australia to handle amounts
of 1/10,000 of the base currency?

Perhaps I am too old-school, but IMO there are some very clear
instances when performance or architecture considerations mean that
the database needs to include business rules, and so stored
procedures are required. In those cases I would rather be dealing
with a true decimal data type than mucking about with integers, or
some user-defined data type.

If your Rails application isn’t the only application directly
accessing the DB, you’ll not get too many people to disagree with
your statement. If your Rails application is the only application
directly accessing the DB, then I think many people will agree that
you’ll save yourself a lot of grief by losing the stored procedures
and providing REST based web access where other applications need
access to the data.

Also, I had a look at the Money type and I have yet to find a clear
description of exactly how it will help me. The existing docs seem
to be written for API developers, which I am not, and so are a
little hard for me to understand.

Well, it provides methods for converting between and formatting
different currency types, and eliminates problems with float values
that were the originally stated concerns of this thread.


– Tom M.

Tom,

Thanks for your response, I think we agree on the technical side, but
see
the business needs differently.

On 4/10/06, Tom M. [email protected] wrote:

On Apr 9, 2006, at 4:56 PM, Angus McDonald wrote:

Personally it’s because I want my database schema to represent
reality, and in the case of payroll applications (in Ausralia at
least) hourly rate is typically stored to 4dp, yet it is a currency
field. It is also the way legacy databases are often setup.

Ah, that’s interesting. Is it typical in Australia to handle amounts
of 1/10,000 of the base currency?

Well 1 hour = 1/9880 th of a year, so yes. Take a salary of $60,000. You
can
express that as an hourly rate of $6.07 or $6.0729. The first when
calculated back out and rounded to 2dp gives you $59,971.60, the second
gives you $60,000.25. The extra $28.65 may not mean much to you, but to
a
payroll person every cent counts! They are allowed to be out by less
than a
dollar (provided it is in the employee’s favour), but $28.65 is too far
from
the intended salary.

directly accessing the DB, then I think many people will agree that
you’ll save yourself a lot of grief by losing the stored procedures
and providing REST based web access where other applications need
access to the data.

Yes, one clear case is when you need to share with other legacy systems
(and
you don’t want to wrap their DB access through a web service or go full
SOA). The other is when you need to process millions of records, and
whacking them through a loop in ANY language is not going to be
efficient -
whereas a set of SQL statements run from a stored procedure might be.
Yes,
you could run those SQL statements from an outside program, but you take
a
performance hit shifting data over the network when it could be kept
within
the database.

Also, I had a look at the Money type and I have yet to find a clear

description of exactly how it will help me. The existing docs seem
to be written for API developers, which I am not, and so are a
little hard for me to understand.

Well, it provides methods for converting between and formatting
different currency types, and eliminates problems with float values
that were the originally stated concerns of this thread.

Cool! I will try to absorb more about it for when I just want a little
ecommerce in my web apps. My purpose in starting this thread was simply
to
leave some evidence for future newbies as to how it could be done (I had
surfed much of the archive looking for something similar) - it may not
be
the best ‘Rails’ solution, but at least it will work.

– Tom M.

Gee, this is embarrassing …in my response to Tom I spouted off some
incorrect numbers. It may not be of much interest to anyone else, but
I’d
hate to have someone come across that email and think a $6 hourly rate
leads
to a $60,000/year salary! The quoted text is:

On 4/10/06, Angus McDonald [email protected] wrote:

dollar (provided it is in the employee’s favour), but $28.65 is too far from
the intended salary.

What I should have actually said was:

Well 1 hour = 1/1976 th of a year, so yes. Take a salary of $60,000. You
can

express that as an hourly rate of $30.36 or $30.3644. The first when
calculated back out and rounded to 2dp gives you $59,991.36, the second
gives you $60,000.05. The extra $8.64 may not mean much to you, but to a
payroll person every cent counts! They are allowed to be out by less than a
dollar (provided it is in the employee’s favour), but $8.64 is too far from
the intended salary.

Thanks,

On Apr 10, 2006, at 5:36 PM, Angus McDonald wrote:

$60,000. You can express that as an hourly rate of $6.07 or
$60,000. You can express that as an hourly rate of $30.36 or
$30.3644. The first when calculated back out and rounded to 2dp
gives you $59,991.36, the second gives you $60,000.05. The extra
$8.64 may not mean much to you, but to a payroll person every cent
counts! They are allowed to be out by less than a dollar (provided
it is in the employee’s favour), but $8.64 is too far from the
intended salary.

Funny, can’t see the forest for the trees!

Question: Why store a yearly salary as a per degenerate
hour rate rather than an actual salary, whatever that
is and calculate hourly rates on-the-fly with floats?

I understand why you’d need to convert to that once in
a while for time off, etc. I also understand the need
to avoid floats for many purposes due to rounding and
accuracy problems.

But, you could calculate the per hourly rate with floats
on the fly and store the calculated total?

For instance, let’s take your example and calculate a
one week paycheck minus 1 hour:

Salary = $60,000/year

1 hour:
$60,000/(52*40) = 28.846153846153847

For for week:

$60,000/52 - 28.846153846153847 = $1,125

In the salary model you could use STI for
all the different salary types (hourly,
weekly, bi-weekly, semi-monthly, monthly,
yearly, etc.) and have methods to calculate
interim payments from the actual salary,
on the fly.


– Tom M.

On Apr 10, 2006, at 10:21 PM, Angus McDonald wrote:

BTW, I don’t have my Ruby/Rails books here, what is STI?
Single Table Inheritance


– Tom M.

Tom,

On 4/11/06, Tom M. [email protected] wrote:

Funny, can’t see the forest for the trees!

Question: Why store a yearly salary as a per degenerate
hour rate rather than an actual salary, whatever that
is and calculate hourly rates on-the-fly with floats?

Mainly because payroll people seem to regard actual salary as an
imaginary
number, and hourly rate the only one that really counts! Bear in mind
that
for hourly rate employees you need to work with hourly rates anyway, and
they are often specified by law to 4dp, so payroll people would rather
have
just one way of calculating pay (hourly rate * hours * percent of
ordinary
rate).

I understand why you’d need to convert to that once in

a while for time off, etc. I also understand the need
to avoid floats for many purposes due to rounding and
accuracy problems.

But, you could calculate the per hourly rate with floats
on the fly and store the calculated total?

That would certainly work, although the number of times you would have
to do
that calculation is higher than you might think. You rarely get nice
numbers
to work with either, for example someone’s salary may be $62,314 and
their
standard weekly hours 37.5, and their overtime rate is 125% of their
ordinary hourly rate (and yes, salaried employees sometimes do get
overtime,
or other hourly rate penalties).

For instance, let’s take your example and calculate a

In the salary model you could use STI for
all the different salary types (hourly,
weekly, bi-weekly, semi-monthly, monthly,
yearly, etc.) and have methods to calculate
interim payments from the actual salary,
on the fly.


– Tom M.

That certainly works if Rails is the only application using the data,
but
when you are webifying a legacy application (or even just moving an
existing
web app to Rails) you don’t always have the luxury of working with that
sort
of object model.

At the end of the day I’m just glad that the solution is only 3 lines of
code per affected column, with a couple of extra requires lines per
model.
In many other languages that would have been far harder to handle! It
also
allows me to concentrate on what I really need to work on and not worry
about where floats might have introduced calculation errors into my
code, so
I will probably use this for my personal financial apps, unless that
money
data type has some compelling functionality I really need (I will be
looking
into it).

BTW, I don’t have my Ruby/Rails books here, what is STI?

On Sunday 09 April 2006 16:41, Tom M. wrote:

I’m not clear why everyone wants BigDecimal support.

Tom,

BigDecimal support is absolutely critical for financial applications.
My
company produces sites almost exclusively for the finance sector. The
values
we calculate must be accurate because these businesses depend on it.
Also,
we deal with both external data and our own legacy data must be able to
handle that with the same accuracy.

Do you need larger numbers than the Money gem can
handle?

Money give you the accuracy by storing as integer,
and gives you a head start on multi-currency as well,
and integrates with Rails perfectly via composed_of.

I see the issue as not so much what people are doing with Rails right
now, but
what they aren’t doing because they currently can’t. No bank is going
to
write their online banking in Rails if it can’t deal natively with
decimal
values.

I’ve been working on a decimal patch for a few weeks, and I’ve given
thought
to looking at the Money gem and see how easily it could be adapted to
deal
with BigDecimals too. That way it would be a general purpose wrapper
and
abstract the underlying integer/decimal nicely.

Just my £0.02 (hoho)

Ashley

On Sunday 09 April 2006 14:53, Angus McDonald wrote:

So once I worked it out for myself, I posted the solution code on my blog .
I really hope this helps someone else out there because it caused far too
much pain for me to want to have gone through it just as a personal
exercise.

Hi Angus

I saw your solution and it’s very straightforward! However there are
other
issues that mean decimal support must be handled by the database adapter

most related to generating SQL to modify the database.

I just posted a link to the patch (
http://dev.rubyonrails.org/ticket/4274 if
you missed it ). I’m on a personal crusade to get decimal support in
Rails.
In my humble little opinion, it is absolutely 100% inescapably critical!
This one feature would completely preclude us from using Rails due to
the
nature of the data we deal with.

Also here is a little tip based on something I saw on your blog: if you
want
readable BigDecimals do this

x = BigDecimal(“499.95”)
puts x.to_s(“f”)
=> 499.95

(Personally I still wonder why it doesn’t default to scientific notation
instead of the 0.xxx format)

Ashley

On Apr 12, 2006, at 2:49 AM, Ashley M. wrote:

On Sunday 09 April 2006 16:41, Tom M. wrote:

I’m not clear why everyone wants BigDecimal support.

Tom,

BigDecimal support is absolutely critical for financial
applications. My
company produces sites almost exclusively for the finance sector.
The values
we calculate must be accurate because these businesses depend on it.

Oh, I understand that part entirely. That’s why I suggested the Money
class.

Also, we deal with both external data and our own legacy data must
be able to
handle that with the same accuracy.

And that largely explains it. Money won’t work with existing schemas
that have
decimal values.


– Tom M.

Ashley,

On 4/12/06, Ashley M. [email protected] wrote:

Hi Angus

I saw your solution and it’s very straightforward! However there are
other
issues that mean decimal support must be handled by the database adapter -
most related to generating SQL to modify the database.

That could well be true. I have yet to delve into that part of Rails
much
yet.

I just posted a link to the patch (
http://dev.rubyonrails.org/ticket/4274if

you missed it ). I’m on a personal crusade to get decimal support in
Rails.
In my humble little opinion, it is absolutely 100% inescapably critical!
This one feature would completely preclude us from using Rails due to the
nature of the data we deal with.

I had seen that, and if I was using PostgreSQL or SQL Server it would be
a
big help. But I’m stuck with MySQL, at least at home. The payroll apps
I’ve
written have been SQL Server based, and this does help with that.

Also here is a little tip based on something I saw on your blog: if you
want

readable BigDecimals do this

x = BigDecimal(“499.95”)
puts x.to_s(“f”)
=> 499.95

(Personally I still wonder why it doesn’t default to scientific notation
instead of the 0.xxx format)

Yeah, I originally had lots of .to_s(“F”) sprinkled around my app, then
I
realised that DRY would suggest putting it in the one place (so I made
price
a string) and then I realised that because I always displayed it with
dollar
signs etc. I could just use one function to do the conversion and leave
price as always a BigDecimal, which will make calculations easier.

Cheers!

Also, I had a look at the Money type and I have yet to find a clear
description of exactly how it will help me. The existing docs seem to be
written for API developers, which I am not, and so are a little hard for
me to understand.

I think that you can find any number of applications that need to work
with non-integer
and non-float numbers, as provided by databases with these fixed point
numbers.
Currently Ruby itself does not support this type. Float, BigDecimal
(which is kind
of a big Float), Integer, Rational etc. are more or less useful
approximations,
but not always the right way to go. Sometimes even kind of wrong.

Using Money helps in some cases, but these columns are not always
amounts of Money
anyway and it is preferred if the database can be accessed with SQL and
show
the values the way they are meant in the business logic.

So for these kind of cases, long-decimal could become helpful.
gem install long-decimal
use:
require “rubygems”
require_gem “long-decimal”
x = LongDecimal(“3.565”)
y = LongDecimal(“4.800”)

Best regards,

Karl

on the fly and store the calculated total?

$60,000/52 - 28.846153846153847 = $1,125

In the salary model you could use STI for
all the different salary types (hourly,
weekly, bi-weekly, semi-monthly, monthly,
yearly, etc.) and have methods to calculate
interim payments from the actual salary,
on the fly.

Often in finance applications there are pretty fixed rules about
how rounding is to be performed. They might not make sense, because
it is easy to find something smarter, but often they are kind of
hard rules that need to be obeyed in order to have a correct operation
according to some accounting standards. It is very important to take
these rules serious, even if they are sometimes silly. Consitency
across finance applications of different generations is something
you gain by this.

Best regards,

Karl

BigDecimal is for floating point numbers with arbitrary precision.
It uses decimal not dual system as internal representation.

LongDecimal is for fixed point numbers with arbitrary precision, also
based on decimal system.

So LongDecimal is the exact match of the type found in the database.
Think of it as an Integer divided by some power of 10. For finance
application it is important to have the exact rounding behaviour with
a fixed number of decimal digits.

A BigFloat class that uses the common dual system for floating point
numbers
of arbitrary precision is currently not available for Ruby. But I think
it would come in the future, if C-libraries like GMP, LIBFR or LIBFI
were
integrated into some Ruby-library. For the time being BigDecimal serves
their purpose very well, because the internal representation is not so
important when doing floating point arithmetic. Dual (binary)
representation
is better in terms of speed, but the decision to use decimal
representation
made it more transparent, thus for example easier to debug.

If LongDecimal had been already there, something like Ruby’s BigDecimal
to work with arbitrary precision floating point numbers would have to
be developed now. Both are needed.

Best regards

Karl

What advantage does long-decimal has over BigDecimal?

It seems that BigDecimal is just the thing needed for database types
Decimal(M,N)…

izidor

P.S. AFAIK, BigDecimal is not BigFloat - it has its numerical value
represented in decimal, not binary. That’s why it is BigDecimal…