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

Thanks to the three of you for your thoughts, those are very very helpful
perspectives that are going to help me design this. One note, I won't have
to worry about multiple currencies on the internal bookeeping side of
things, which is a major plus.


Jorge Godoy-2 wrote:
>
> 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>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>

--
View this message in context: http://www.nabble.com/Multiple-currencies-in-a-application-tf2605959.html#a7498357
Sent from the PostgreSQL - general mailing list archive at Nabble.com.