How to store and work with money in code and database?

Question

Database design

Issue resolved. It is preferable to use int both in terms of speed and in terms of the size of the data that can be stored in it. Below are the details.
Let's try once and for all to discuss and understand how to store and work with sums of money.
I studied the issue and realized that people are divided into two camps:
1) Storage in int
2) Storage in decimal
Let's say that it is enough for me to conduct billing in rubles with an accuracy of kopecks.
Storage in bigint
Pros:

• No floating point - fewer inaccuracies
  
• It is possible to produce standard mat. operations and not be afraid that you will lose a penny somewhere
  
• mithraen : Mat. transactions go faster (From the questioner - I checked. The calculations are below)
  
• gleb_kudr : Here is the international currency standard en.wikipedia.org/wiki/ISO_4217
  It shows that the number of decimal places can be different. This means that for ease of development, everything should be stored in the minimum fractional monetary unit of the currency, converting upon withdrawal (i.e. to int)
  

Minuses:

• Remember to always multiply/divide by 100
  

Storage in Deciamal/Numeric
Pros:

• Keep it natural
  
• No need to extra steam when withdrawing
  

Minuses:

• With careless work (not through bcmath), you can make a mistake when multiplying or dividing
  
• Working through bcmath is slower
  
• Different currencies have a different number of decimal places — if you have a multi-currency system, there will be data redundancy. You will have to make more than 2 decimal places, but not everyone will need them
  
• Robert Martin : "It's almost a crime to use floating point numbers to represent money"
  

I call everyone to the discussion, and I will add points. I really want to end this issue. And in one and the other approach there are inconvenient things.
Speed ​​measurements
I will give examples in php.
Working with floating point

<?php
$a = 1.2;
$b = 3.4;

for ($i=0;$i<1000000;$i++) {
    $c = bcdiv($a,$b,2);
}

We launch:

[email protected]:/home$ time php 1.php

real    0m3.490s
user    0m3.468s
sys     0m0.020s

Working with Int

<?php
$a = 11.2;
$b = 3.4;

$a *= 100;
$b *= 100;

for ($i=0;$i<1000000;$i++) {
    $c = $a/$b;

    // Избавляемся от лишних чисел
    $c *= 100;
    $c = (int)$c;
    $c /= 100;
}

We launch:

[email protected]:/home$ time php 2.php

real    0m0.562s
user    0m0.540s
sys     0m0.020s

The 6 times speed difference is obvious.

Answer 1

Here is the international standard for monetary units en.wikipedia.org/wiki/ISO_4217
It shows that the number of decimal places they may have is different. This means that for ease of development, everything should be stored in the minimum fractional monetary unit of the currency, converting upon withdrawal (i.e. to int)

Answer 2

There is a good discussion on stackowerflow

Answer 3

int is more convenient.
We simply believe that all the amounts we have are indicated in kopecks. And all mathematical operations are performed extremely quickly. And the concept of "rubles / kopecks" makes sense only when entering / withdrawing.
Convert there.

Answer 4

In our company, all funds are stored in int.
Dividing by 100 does not bother a bit, because already used to. And even the thought of storing it in another form does not arise.
Yes, and floating point formats are inherently redundant in terms of storage and work with money, although in our time this redundancy is unlikely to be at least somehow noticeable :)

Answer 5

> If you write something like "money cannot be stored in floats" - write why exactly.
How why? Loss of precision and constant rounding, of course. And very funny calculations near zero.
But what does float have to do with it when you consider int and decimal?
Another “pro” of int is a stupid integer operation. Unlike decimal, which is not a simple data type.

Answer 6

we use oracle+number
we don't experience any problems

Answer 7

I have a question about debit amounts. How to store negative amounts in the database? With a minus sign or add a record type debit & credit ? But how then to quickly calculate the sum of all records with one query?

Answer 8

In some cases, the sum can be stored in a float, for example when these are intermediate results. And directly records with monetary amounts are needed in an integer format, since there are no fractional kopecks in accounting, only whole ones. Well, when performing operations, rounding must be done carefully.

Answer 9

This question still torments us! )
Look, when two decimal places, everything is fine, everything is clear. But what about, for example, if I need to store 7 characters before and 7 characters after. Would bigInt be better than numeric (decimal)
I'd be grateful if someone could give me an answer, taking into account the peculiarities of the PostgreSQL database.

Answer 10

If you are going to keep money and do currency exchange - I recommend the presentation 2013.zeronights.ru/includes/docs/Adrian_Furtuna_-_...

Answer 11

Here https://www.postgresql.org/docs/9.5/static/datatyp... (section 8.1.2)
recommend NUMERIC