Re: Multiple currencies in a application - Mailing list pgsql-general

Alban Hertroys <alban@magproductions.nl> writes:

> A few things you'll probably want:
> - Store prices in your db with their original currency
> - Make sure you have up-to-date conversion rates (how up to date that
> needs to be is up to you)
> - Calculate actual prices on demand

- Keep the highest precision on prices you can, if you can get sub-cents its
  better

You'll have to worry with rounding / truncation rules (they differ from
country to country so you'll have to either state what you do and why this
might not be accurate or you'll have to code it some way that you can do the
right thing all the time...).

If you store up to cents, then you might end up loosing money or charging too
much.

If you can get to the fourth decimal place you'll have a safe zone to work
with cents if you deal with a few thousands units of the product.  The more
you sell / buy, the more decimal places would be interesting to have.  Of
course, I'm supposing that cents are important, if you're selling / buying
products that cost thousands or millions of <currency here>, then this looses
that importance (this is relative to how much monetary units the value
represents in the destination currency).

> We are satisfied with daily updates to our conversion rates, which we
> store in a table. Conversion isn't too difficult that way.

With webservices you can get almost real time rates and you don't even have to
store these rates on your database (even though it is nice to have it for
summaries and reports and also for auditing operations).

> Say you want to convert the price of a product from dollars (the
> original currency) to euros, your query would look something like this:
>
> SELECT price * target.rate / source.rate
>   FROM products
>    INNER JOIN rates source ON (products.currency = source.currency),
>     rates target
>  WHERE products.id = 1234
>    AND target.currency = 'euro';
>
> I don't think you'll need any functions, unless to retrieve real-time
> conversion rates somehow. Otherwise a cron job will do nicely.

Yep...  There are some apps that request for the rate to be used when you
login (they usually suppose you'll be working with two currencies: a reference
currency and a local currency).


I've also seem projects that have an artifical value for each product and then
apply conversion rates from this value to any currency they want.  Something
like making 1 unit equal to 10 cents, so a product that costs 10 <currency>
would be stored as costing "100 units".  Then you just have to have a
conversion table from the basic unit value to the currency you want to deal
with.

This makes it easier to update prices and do some historical analisys besides
making it easier to make a comparative analisys of each market.


I've also seen systems where each "currency" (local, actually) can have a
different price.  It makes a lot of sense since paying something like 2
dollars for a coffee on the US doesn't sound all that much but it would be a
robbery here in Brazil if the value of such product was converted to reais :-)


Last but not less important you should also consider how you're going to add /
represent S&H costs, import / export taxes, etc. and how this will impact on
the value you'll be showing to the user.


This is just the tip of the iceberg, but I hope it helps a little to see what
is important or not for you.

--
Jorge Godoy      <jgodoy@gmail.com>