What is the best way to store monetary values in SQLite?

Navigation
10

  • this post about the lack of precision of double , and practically all the links of the post links.

    So I realized that this was a vulnerability of my software in production (since I have rounds for every corner before doing any kind of calculation related to monetary values). So before you have bigger problems with the clients pocket. I resolved to refactor my system using BigDecimal for all attributes related to monetary values.

    When I reached the database access layer, I came across a problem / question about how to store these values reliably and accurately in SQLite Database?

    Searching I found some possibilities:

  • Save in a INTEGER column, in the form of cents, for example: BigDecimal valor = new BigDecimal("1.67") , in the base would be: long valueInCents = valor.multiply(new BigDecimal(100)).longValue() , which would result in 167 in the database column, requiring the reverse process when obtaining the value of the database again. (This is what I found most effective, practical and interesting so far);
  • Save in two columns INTEGER one to the integer part of the value and another to the decimal part. (This is more comprehensive because it would give you the freedom to use as many decimal places as necessary up to the limit of% s of SQLite that I think is 18, but for monetary values maybe an unnecessary effort, if you only work with 2 pennies);
  • Save to a column of type INTEGER and always convert from TEXT to BigDecimal and String to String . (This would be the most practical solution since BigDecimal has a constructor for the value in BigDecimal , but nevertheless it is the solution that least pleases me since it would not have the numerical value in the database, so any calculation with this column should be done via application).

    • Questions

    • Would the column of type String , or any other decimal variant, be accurate to store this REAL value while maintaining accuracy without major problems?
    • Which of the three possibilities mentioned above (or some extra solution) would be more appropriate and efficient to store monetary values (with only 2 pennies)?
    asked by anonymous 05.08.2015 / 16:57

    1 answer

    8

    SQLite% type% is real , do not use.

    Choosing one for you would fall into the belief. It depends on what you need, how comfortable you are to work. I'll point out the issues and let you know that there is no better way to resolve this than to change SQLite.

    • The first one has the difficulty of having to deal with the decimal places in the code (Java or SQL). It is not the end of the world but there are those who do not like it. It even gives you the ability to create a class that abstracts this for you, so you do not have to worry about the application everywhere. But in queries SQL has to always remember.

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

    • The third one also has this problem of having to deal with. need to convert to make calculations in every use. And you have to be careful not to convert to numeric binary (type double ) when you use it, even if you do not want it, then you use it as real or real . You can do the calculations with her but it's work. Without a better form of abstraction I would not go into it, but it's taste.

    Other sites has responses for this and it is almost unanimous that the former is better. I also think.

    Otherwise, 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 this will solve easy. Not really. When you multiply a number with 2 houses and another with 4 houses, the result should have 2, 4, 6 or another number of houses? There will be a loss in rounding and some penny or fraction of penny will possibly be lost. What to do with him? If you divide a value into installments, will there be pennies because of rounding, where should it be placed? In the first installments? In the last? Interleaved? Only in the first or only in the last one? 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 addresses this and somehow abstracts it, but this will have to be done through policies, decorators, strategies, or other forms, you can not create a class double or the existing Money , for example and it will know how to handle everything.

    The Joda Money helps in this but does not solve everything.

    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 cents or quantities could be normalized to the minimum capacity to have no decimal places. Still the problem described in the previous paragraphs would have to be addressed.

    This answer holds true for anyone who is using SQLite with other languages or operating systems or devices.

        
    05.08.2015 / 17:14
    What is Ellipsis in Python? Class nested in Java