Storing "money" in databases

Rails
1

Hiall,

I’m wondering what’s common practice when dealing with money values
stored in databases. I like the idea of storing all the values as
integers, i.e. all the values in eurocents not euros. Then I need to
multiply all values the users enter by 100 before storing them in the
database. Now the question is, what’s the most efficient and dryest
way to do this? I’m not sure if I want to override every accessor
activerecord generates for me …

Any ideas?

cheers
Martin

2

Martin,

Thus far (at least for dollars) we have been creating a decimal column
and using that to store monetary values. I am not sure how the euro
works but that works for us. Good luck!

–
Sincerely,

Robert D.
Project Director
Atlantic Dominion Solutions, LLC

321.356.6855

Visit our blog for all the latest at http://www.techcfl.com/blog

3

On Jun 12, 2006, at 2:59 AM, Martin G. wrote:

Any ideas?

cheers
Martin

Check out the Dollars and Cents plugin: http://
agilewebdevelopment.com/plugins/dollars_and_cents

–
Benjamin C.
http://www.bencurtis.com/
http://www.tesly.com/ – Collaborative test case management
http://www.agilewebdevelopment.com/ – Resources for the Rails community

4

On Jun 12, 2006, at 6:00 AM, Benjamin C. wrote:

activerecord generates for me …

Check out the Dollars and Cents plugin: http://
agilewebdevelopment.com/plugins/dollars_and_cents

Interesting. Hadn’t heard of that one.

I have used the Money gem however, and like it quite a bit.

gem install money

It stores in cents, and has currency conversion and output formatting
as well.

You can use ActiveRecord composition to make it play nicely with the DB.

–
– Tom M.

5

The common practice is a decimal field with places to the right of the
decimal point.
Usually two places. Standard SQL has a decimal data type. There is a
reason for this.
They would not have gone to all the trouble if it were a minor point.
Numerous machine
hardware architectures have a decimal type in addition to float and
integer.

Do you dare to test floating point numbers for equality?

While the Ruby library has a BigDecimal data type it has no “Ordinary”
decimal type. Ruby
has integers and floats. So Rails uses floats for money. This is not
the right answer.
This is the biggest hole I know of in Ruby and Rails. Ruby apparently
needs a better
regular-decimal type. I guess the BigDecimal type has problem. I’d
guess the problem is
performance. Another is formatting the output of BigDecimal numbers and
rounding.

There are some good articles around somewhere that describe in detail
the reasons for the
innovation of fixed-decimal numbers and why floats are not adequate for
financial data
processing. Mostly to do with handling of rounding and the fuzziness of
comparison. See:

Warren S.

6

Given issues with representing floats accurately in binary I was under
the impression that any app that requires accuracy would stick with
ints.

The only time you really need to convert to the regular format would be
displaying, yes?

Converting the inputs sounds like a good place for a Insert/update
trigger.

Cheers
Chris

7

On 6/12/06, Warren S. [email protected] wrote:

regular-decimal type. I guess the BigDecimal type has problem. I’d guess the problem is
performance. Another is formatting the output of BigDecimal numbers and rounding.

I think choosing float over bigdecimal was more of an
oversight/shortcut that’s never been fixed.

I never tested BigDecimal performance, but it can’t be that bad, and
certainly not horrid enough to make me suddenly stop caring about the
accuracy I had in mind while designing my db.

Isak