|
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
|
