On 15 Dec 2018, at 10:15am, Frank Millman <fr...@chagford.com> wrote:

> Simon Slavin says ‘Currency amounts should be stored as integers’. Does this 
> apply to sqlite3 specifically, or is that your recommendation for all 
> databases?

For anything that goes near a computer.  I used to work with international 
banking software.  Any time we put out a tender for an interface and the bidder 
treated currencies as REAL we knew we'd be able to find a calculation which 
invisibly lost or gained a fraction. Even with division we could trip up their 
software.  It's an inherent problem with using binary operations on a base-10 
fraction.

The only time you treat currency units as real is when you're doing 
calculations which are inherently real.  As when you're given a yearly interest 
rate of 4% and want to know how much interest you'd get for 820 days.  And even 
with that you do the calculation and convert the answer to an integer, using 
whatever rounding/truncating algorithm your banking authority has approved.

It doesn't involve much less work once you've decided to do it.  The big 
problem occurs only when taking a system which was designed (possibly by 
someone else years ago) for fractions and converting all the data.

Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to