postgresql - Currency/monetary values -- How to store in a DB & transfer using JSON? -

what best practices storing currency/monetary values in db, processing them in server-side app, , sending them down browser via json api?

i've figured 2 approaches, i'm not sure how weigh out pros & cons:

store values integers in smallest monetary unit

• basically means database store monetary value in cents / paise / shillings / etc.
• the server side app map value regular integer variable.
• the json api represent value regular json number.
• the downside of approach have divide 100 before displaying monetary value user , multiply 100 before storing user input.
• possible gotchas: how define smallest monetary unit? on basis of 2 decimal points or four? there currency not have 100:1 ratio, i.e. 100 cents = 1 dollar.

store values decimal/numeric fixed precision & scale

• the db stores monetary value decimal/numeric type fixed precision & scale, eg. numeric(10,2)
• the server-side app maps these special objects can preserve precision & scale across computations, eg. bigdecimal in ruby.
• the json api exchanges these values strings , not numbers. because numbers automatically parsed floats json parser, causing loss in precision.
• the downside of approach computations on server-side need happen within boxed data-type (possibly slower) , json parsers needs aware of the fact strings not strings, numeric values.
• the upside of don't need multiply , divide 100.

is there accepted best practice this?

there 2 additional gotchas both practices.

firstly, not currencies need 2 decimals. many need three. many more need zero. , field-specific apps, e.g. finance , forex, need 5 or 6.

secondly, currencies have colorful conversion rates subdenominations. old-school rupees, instance, converted 16 annas, 64 paise or 192 pies. luckily, 2 countries retain kind of crazy conversions rates if wikipedia go -- mauritania 1 ouguiya = 5 khoums, , madagascar 1 ariary = 5 iraimbilanja.

http://en.wikipedia.org/wiki/decimalisation

the point, though, shouldn't us-centric in assumptions if plan ever localize app. @ least, consider use-cases you've 0, 2 , 3 decimals, , chew bit on path you'd want take if ever went international. in case.

as aside, note numeric type in postgres can stored without specifying precision. takes no more room so, since it'll reside in extended storage when large same. and, varchar better varchar(n), it'll faster because you'll skip built-in precision check when storing numbers (which, in case, needs sane indeed).

as of 2 approaches describe best, i've seen more of second -- in simplified form.

i'd stress 3 points:

1. storing currency numeric, or without precision, makes sense in db, sake of not introducing rounding errors when generating accounts , reports. (there built-in money type, too, i've never seen used in practice sorts of valid reasons.)

2. speed should least of worries if need kind of precision overflows bigint or double-precision float (the latter of used js , json floating point numbers, if memory serves). if case, arbitrary precision maths way go, 1 end other.

3. typical apps manipulate typical currency amounts never run these overflow limits. take cold hard here. unless you're planning store amounts in quadrillions of dollars, converting unbounded numerics string , pass them around in json sign you're over-engineering app. if case, stick double-precision floats , ship app.