Storing currency in a database

[NotionCommotion]

I wish to store money (value/currency) in a database, and thinking of following ISO-4217.  For example, $50.45 is represented as 5045/USD and ¥227 is represented as 227/JPY.

If I use two columns, suppose I should use big integer and char(3) as it gets me up to +/- 4.6 quintillion dollars which is pretty big. It would make some things simpler to store as a single string such as USD:5045 and JPY:227, however, I have some concerns.  Instead of me making up my own format, is there any recognized format such as what RFC3339 is for time?  I recognize it makes sorting by cost a little challenging, but since exchange rates are not fixed and there is no way to normalize the data like using UTC, it will be challenging regardless.  Any other concerns or ideas?

Thanks

 

A couple ISO-4217 examples.

 

[ginerjm]

I would use a decimal type in the table and then add a currency name column.  Then I would write a function that handles the exchange rates separately but how to know when it needs updating is your problem.  Leave out your Code column and Num column since the code can be part of your function and the num is not necessary with a true mysql decimal type.

[requinix]

There is no standard hybrid format for currency and amount. Use a non-float column that stores the amount measured in the lowest units (so $1.23 is 123 and ¥123 is also 123) and a string column for the currency code.

[NotionCommotion]

1 hour ago, requinix said:

There is no standard hybrid format for currency and amount. Use a non-float column that stores the amount measured in the lowest units (so $1.23 is 123 and ¥123 is also 123) and a string column for the currency code.

There should be!  Why not go per ISO-4217 regarding the unit and decimal so for US units are dollars and decimals are 2 and for Japan units are yen and decimals are zero, and I don't need to come up with with what I think the lowest units are?  I suspected that trying to combine amount and units in a single string is a bad idea and sounds like you agree, however, would like to still hear your reason why not to do so.

[Phi11W]

9 hours ago, NotionCommotion said:

There should be!  

Obligatory XKCD Reference: Standards

9 hours ago, NotionCommotion said:

I suspected that trying to combine amount and units in a single string is a bad idea and sounds like you agree, however, would like to still hear your reason why not to do so.

Simply, you want to arithmetic on the values. 
Addition, subtraction, multiplication of numeric values that represent monetary amounts in different currencies. 

If those values are "buried" inside String values inside a single database field, then you have to waste processing time pulling those values apart so that you can "get at" the numeric part and then do your arithmetic on that. 

Databases are really, really good a finding bits of data and putting them together. 
They are [all] generally rubbish at finding big chunks of data and pulling them apart again. 

This is a case where the storage representation of these values (two fields, one numeric value, one character currency code) is different from the way that you or I might choose to think about them. 
That representation - the one that we would use - should be delivered by the Application, interpreting what's stored in the database into what we are used to seeing (and reinterpreting values going the other way, from what we use into the database). 

Regards, 
   Phill  W.

[requinix]

19 hours ago, NotionCommotion said:

There should be!  Why not go per ISO-4217 regarding the unit and decimal so for US units are dollars and decimals are 2 and for Japan units are yen and decimals are zero, and I don't need to come up with with what I think the lowest units are?  I suspected that trying to combine amount and units in a single string is a bad idea and sounds like you agree, however, would like to still hear your reason why not to do so.

It's just regular old normalization: if you have two pieces of data then you need two columns to hold them.

Slight clarification to a thing I said: non-float with the smallest unit, so for an integer column you'd have $1.23 as 123 but with a decimal(_,2) column you'd have 1.23 perfectly fine. But with a decimal you have to care about the number of decimal places, and as pointed out different currencies use different scales.

[NotionCommotion]

4 hours ago, requinix said:

It's just regular old normalization: if you have two pieces of data then you need two columns to hold them.

Yeah, my spider sense told me so but I was just being lazy/dumb.

4 hours ago, requinix said:

Slight clarification to a thing I said...

Unfortunately, I am not dealing with big money but if one were, they should definitely not be using floats and if anything but US currency,  I believe only integers for the value plus a string for the currency which would use ISO-4217 (hooray, a standard!) to lookup the base currency value and decimal places.

[kicken]

On 1/31/2022 at 4:14 PM, NotionCommotion said:

and there is no way to normalize the data like using UTC

Disclaimer: I've not done any work with multiple currencies so I may be missing something

You can normalize the data, you just do it differently and at different times.

When inserting the data, normalize it to a fixed-point integer or decimal notation.  Pick a point that gives you the precision you need and scale everything to that.  In your table the most precise currency is 3 decimal places, so you could use that as your precision.  $50.45 would become 50450 and ¥227 would become 277000.  Your php code can deal with enforcing rules like JPY can't have decimals.

Then when querying your data, you can have another table that stores the exchange rate from whatever $currency to your standardized currency.  Join that able when querying your results and multiple the rate to get your standardized price and sort by that.

drop temporary table if exists payment;

create temporary table payment (
    id int not null auto_increment primary key,
    amount bigint not null,
    currency char(3) not null
);

insert into payment (amount, currency) values 
    (50.45*1000, 'EUR') -- 50.45 EUR
    , (277*1000, 'JPY') -- 277 JPY
    , (50.45*1000, 'MXN') -- 50.45 MXN
    , (50.45*1000, 'RUB') -- 50.45 RUB
    , (5.045*1000, 'TND') -- 5.045 TND
    , (50.45*1000, 'USD');-- 50.45 USD
    
drop temporary table if exists exchange_rate;
create temporary table if not exists exchange_rate (
  currency char(3) not null primary key,
  rateNumerator bigint not null,
  rateDenominator bigint not null
);

insert into exchange_rate (currency, rateNumerator, rateDenominator) values 
    ('EUR', 11276, 10000)
    , ('JPY', 00871637, 100000000)
    , ('MXN', 0486747, 10000000)
    , ('RUB', 0130442, 10000000)
    , ('TND', 346070, 1000000)
    , ('USD', 1, 1);

select 
   p.id,
   p.amount,
   p.amount/1000.0 as decimalAmount,
   p.currency,
   floor((p.amount*xr.rateNumerator)/rateDenominator) as usdNormalizedAmount,
   ((p.amount*xr.rateNumerator)/rateDenominator)/1000.0 as usdDecimalNormalizedAmount
from payment p
inner join exchange_rate xr on xr.currency=p.currency
order by
	usdNormalizedAmount