30

The infamous question about datatypes when storing money values in an SQL database.

However in these trying times, we now have currencies that have worth up to 18 decimal places (thank you ETH).

This now reraises the classic argument.

IDEAS

Option 1 BIGINT Use a big integer to save the real value, then store how many decimal places the currency has (simply dividing A by 10^B in translation)?

Option 2 Decimal(60,30) Store the datatype in a large decimal, which inevitibly will cost a large amount of space.

Option 3 VARCHAR(64) Store in a string. Which would have a performance impact.

I want to know peoples thoughts and what they are using if they are dealing with cryptocurrency values. As I am stumped with the best method for proceeding.

Community
  • 1
  • 1
Kevin Upton
  • 3,336
  • 2
  • 21
  • 24
  • Upvoted, but I would expect this to have always been a problem with large financial institutions which process very large transactions. I wonder how they handle this? My guess would be the large `DECIMAL`. – Tim Biegeleisen Oct 10 '17 at 04:59
  • This question is way too broad because the answer strongly depends on the algorithms you will use to process the data, and on the performance you need. As a general rule, I think that algorithms based on strings (`VARCHAR`) and computing with `DECIMAL` will be quite slow, but could be understood more easily compared to some fast, but tricky methods which use a combination of `BIGINT` and `DOUBLE`, two doubles or other types. – Binarus Oct 10 '17 at 06:03
  • If you must round exactly the same way the bank(s) do, then you not only need the right number of decimal places, but you also need to know the rounding rules. What is your real goal? If it is tracking "stocks", `DOUBLE`, with ~16 _significant_ digits of precision is probably more than enough. – Rick James Oct 11 '17 at 15:30

1 Answers1

35

There's a clear best option out of the three you suggested (plus one from the comments).

BIGINT — uses just 8 bytes, but the largest BIGINT only has 19 decimal digits; if you divide by 1018, the largest value you can represent is 9.22, which isn't enough range.

DOUBLE — only has 15–17 decimal digits of precision; has all the known drawbacks of floating-point arithmetic.

VARCHAR — will use 20+ bytes if you're dealing with 18 decimal places; will require constant string↔int conversions; can't be sorted; can't be compared; can't be added in DB; many downsides.

DECIMAL(27,18) – if using MySQL, this will take 12 bytes (4 for each group of 9 digits). This is quite a reasonable storage size, and has enough range to support amounts as large as one billion or as small as one Wei. It can be sorted, compared, added, subtracted, etc. in the database without loss of precision.

I would use DECIMAL(27,18) (or DECIMAL(36,18) if you need to store truly huge values) to store cryptocurrency money values.

Bradley Grainger
  • 27,458
  • 4
  • 91
  • 108
  • Thanks for the feedback, I wasnt sure if this question would ever get an answer, haha. – Kevin Upton Feb 03 '18 at 03:39
  • DECIMAL(27,18) will take 20 bytes, 12 bytes for integer part and 8 bytes for fractional part. Right? – sasa May 10 '18 at 11:20
  • 2
    @sasa No, for `DECIMAL(27,18)` there are 27 digits total: 9 before the decimal point (4 bytes) and 18 after (8 bytes), for a total of 12 bytes. – Bradley Grainger May 10 '18 at 14:24
  • 9
    According to Solidity docs, an ETH value declared as uint256 has 256 bits unsigned. That means 32 bytes! So using the above may have some surprising edge cases, especially when working with some tokens that have significantly rager supplies. – Allen Hamilton Nov 13 '18 at 12:10