On 30/03/13 04:08, Gavan Schneider wrote:
Some thoughts.
The current MONEY type might be considered akin to ASCII. Perfect for
a base US centric accounting system where there are cents and dollars
and no need to carry smaller fractions. As discussed, there are some
details that could be refined.
When it comes to this type being used in full blown money systems it
lacks the ability to carry fractions of cents and keep track of
currencies. It also needs to play nicer with other exact types such as
numeric, i.e., no intermediate calculations as real.
Therefore the discussion is really about the desired role for the
MONEY type. Should it be refined in its current dallar and cents mode?
or, be promoted to a more universal role (akin to a shift from ASCII
to UTF)?
If there is merit in making MONEY work for most situations involving
financial transactions I think the following might apply:
- keep integer as the underlying base type (for performance)
- generalise the decimal multiplier of a MONRY column so a specific
MONEY column can be what its creator wants (from partial cents to
millions of dollars/Yen/Other, along with rounding/truncating rules as
required by r the user of his/her external agencies)
- define the currency for a given column and only allow this to change
in defined ways, and specifically forbid implicit changes such as
would arise from altering LOCALE information
- ensure the MONEY type plays nice with other exact precision types,
i.e., convert to REAL/FLOAT as a very last resort
Personally I don't think it is appropriate for the MONEY type to have
variable characteristics (such as different currencies) within a given
column, rather the column variable should define the currency along
with the desired decimal-multiplier and whatever else is required. The
actual values within the column remain as simple integers. This is
mostly based on performance issues. If the MONRY type is to be used it
has to offer real performance benefits over bespoke NUMERIC applications.
Regards
Gavan Schneider
I agree 100%.
In the bad old days when I was a COBOL programmer we always stored money
in the COBOL equivalent of an integer (numeric without a fractional
part) to avoid round off, but we displayed with a decimal point to
digits to the left. So storing as an integer (actually bigint would be
required) is a good idea, with parameters to say how many effective
digits in the fractional part, and how many fractional digits to display
etc. - as you said.
Cheers,
Gavin
