What is the best way to store money in Sqlite?

Question

Li that post on the lack of precision of double, and virtually every link in the post.

Then I realized that this was a vulnerability of my software in production (since I have rounds to every corner before making any kind of monetary value-related calculation). So before having bigger problems with customers' pockets. I decided to refactor my system using BigDecimal for all attributes related to monetary values.

When arriving at the database access layer I came across a problem/question of how to reliably and accurately store these values in the Sqlite Database?

Researching found some possibilities:

1. Save to a column INTEGER, in the form of pennies, for example: BigDecimal valor = new BigDecimal("1.67"), at the base would: long valueInCents = valor.multiply(new BigDecimal(100)).longValue(), which would result in 167 in the database column, requiring the reverse process when retrieving the value from the database again. (This is the one I found more effective, practical and interesting so far);
2. Save to two columns INTEGER one for the whole part of the value and one for the decimal part. (This is more comprehensive as it would give freedom to use as many decimal places as are needed up to the limit of INTEGER sqlite think which is 18, but for monetary values it may be an unnecessary effort if you work only with the 2 pennies);
3. Save to type column TEXT and always converts from BigDecimal for String and of String for BigDecimal. (This would be the most practical solution, since the BigDecimal has a constructor for the value in String, but despite this is the solution that I like least since it would not have the numerical value in the database, so any calculation with this column should be done via application).

Doubts

• The column of the type REAL, or any other decimal variant, would have the precision to store that value BigDecimal maintaining accuracy without major problems?
• Which of the 3 possibilities mentioned above (or some extra solution) would be more suitable and efficient to store monetary values (with only 2 cents. Ex: 1.67)?

Answer 1

The guy real of the Sqlite is the double, don’t use.

Choosing one for you would fall in your opinion. It depends on what you need, how comfortable you are to work. I will point out the problems and letting you know that there is no better way to solve this than by changing the Sqlite.

• The first has the difficulty of having to deal in the code (Java or SQL) the correctness of the decimals. It is not the end of the world but there are those who do not like. You can even create a class that abstracts it for you and then you don’t have to worry everywhere in the application. But in darlings SQL has to always remember.

• The second has essentially the same problem and is more complicated to do right (the formulas will have to be more complex, although you can create classes in Java and functions for SQL to handle this, abstracting the problem). The only advantage I see is to allow very large values, but I don’t know if they’re necessary. Maybe it’s in Zimbabwe. I’ll take the first one, but your case may require this form, but I doubt.

• The third also has this problem of having to treat. It needs to convert to make calculations in every use. And take care not to convert to binary numeric (type real) when you use it, even if you don’t want to, then it’s the same as using the real or double. You can do the calculations with it but it takes work. Without a better form of abstraction I wouldn’t go in it, but it’s taste.

Other websites network has answers to this and it’s almost unanimous that the first one is better, too I think.

Other than this, change Sqlite to support a new data type :)

Everything is a matter of abstraction. If you can make it transparent everything changes. In Java it’s a little easier to do this than in Sqlite.

Some will find that creating a class that abstracts it will solve easy. It’s not like that. When you multiply a number with 2 houses and another with 4 houses, the result should have 2, 4, 6 or other number of houses? There will be a loss in rounding and some penny or fraction of cent will possibly be lost. What to do with it? If you divide a value into installments, will there be pennies left over because of rounding, where should it be placed? In the first installments? In the last? Interspersed? Only in the first or only in the last? Each situation will require a solution. And the intermediate calculations to arrive at a result?

It may be interesting to have a canonical code that deals with this and that somehow abstracts this, but this will have to be done through policies, with decorators, with strategies, or other forms, you can’t create a class Money or the existing Currency for example, and she won’t be able to handle everything.

To Joda Money helps with that but doesn’t solve everything. Java has adopted Joda in its newer Apis.

Remembering that this is important with money but also applies to other values that depend on accuracy. Quantities that can be fractionated of any kind use this. We would be happy if there were no pennies or quantities could be normalized to the minimum capacity for not having decimal places. Still the problem described in the previous paragraphs would have to be addressed.

This answer goes for those using Sqlite with other languages or operating systems or devices.

Behold What is the correct way to use the float, double and decimal types?.