1

I've been reviewing GnuCash's DB design and I found that they are storing amounts as two 64-bit integers, the numerator and denominator of a fraction.

Is there some advantage from a DB perspective to storing financial data this way? I always learned (in MS SQL Server) to use decimal(18,4) for currencies values.

4

There's no advantage from a DBMS perspective, but it may allow better data management, if you are dealing with some unusual form of currency. For example, if you had a currency where the basic unit is the pound, and the pound is composed of 20 shillings, and each shilling is composed of 12 pennies, it would sometimes be impossible to represent an exact number of pennies as a decimal fraction of some number of pounds.

If penny amounts were represented as approximations, round off error would begin to creep in when arithmetic operations were performed.

Such a currency may seem unbelievably quirky, but this is the way British currency worked when Elizabeth II ascended to the throne. There may be currencies in the world today that still work in a similar way.

-1

This is explained in detail in this post (here related to java development, but the goal is to explain why not to use float or double values for representing money):
https://stackoverflow.com/questions/3730019/why-not-use-double-or-float-to-represent-currency

  • 2
    afaik the decimal data type (NOT the double or float data type) was specifically designed to store base-10 decimals accurately. – just.another.programmer Jun 10 '14 at 10:37
  • 2
    Float/double are inexact as @just.another.programmer says. Numeric/decimal are exact. Different problem. – Michael Green Jun 10 '14 at 11:19
  • 1
    The Float/Double is actually relevant to Gnucash's development history. They actually had originally built it using a double datatype, but then fixed it in 2000 for this same problem. See in the change log github.com/Gnucash/gnucash/blob/master/ChangeLog.1 quoting: "2000-10-16 Bill Gribble ... numeric format: the engine's internal representation of values changed from 'double' to 'gnc-numeric', an exact number format." – Joshua Huber Jun 10 '14 at 22:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.