How do you designed your currency exchange rate table?
1 04 Aug 2015 11:19 by u/leixiaotie
I need a help here. At first, I think designing a currency exchange rate is simple and not complicated. I was wrong. Now I need helps to design the currency exchange table.
This is the current design:
| field_name | data_type | description |
|---|---|---|
| exchange_date | datetime | |
| rate_type | varchar | consist of commercial rate, central bank rate, tax rate |
| from_currency | varchar | |
| to_currency | varchar | |
| buy_rate | decimal(32,6) | |
| sell_rate | decimal(32,6) | |
| note | varchar | to hold rate references |
So far I have found several problems:
Exchange rate have different types
Such as commercial, central bank and tax rate. Currently those three are being handled by rate_type.
Cross currency conversion
Say that we have three currencies, GBP, EUR and USD. Now how do you store those conversions: 1) GBP to USD, 2) USD to GBP, 3) EUR to USD, 4) USD to EUR, 5) GBP to EUR, 6) EUR to GBP. Do you handle with 6 rate records which perfectly map the records, or 3 rate records and use multiplication / divison for calculation, or even 2 rate with one of them acts as middleman (ex: GBP to EUR become GBP to USD then USD to EUR) ?
High nominal currency conversion
One of the case here happens with IDR - USD conversion, using the google's exchange rate from web. I test using 1 IDR to USD, the rate is 0.000074. Meanwhile 100k IDR to USD is 7.414050. It lose precision due to different in nominal. How do you solve this issue?
4 comments
0 u/Craftkorb 04 Aug 2015 18:12
OP, why didn't you post this to /v/LearnProgramming or any other subverse?
0 u/leixiaotie [OP] 05 Aug 2015 02:58
What's the different here with [HELP] flair and /v/LearnProgramming? I won't mind to x-post there
0 u/Craftkorb 05 Aug 2015 16:36
In general, "HELP" is for any submission where the OP is seeking for help, no matter the skill level. The lines are quite blurry to be honest when it comes to exactly saying if a question belongs in here, or if it better belongs to some other subverse, like /v/LearnProgramming (For beginner questions), or in this case, some database oriented subverse (Which I don't know). There wasn't really a lot of discussion on this topic yet though.
In general, as moderator I'd like to have a better tool or whatever to direct questions into a subverse where it really belongs, and doesn't get mixed into the tech news and drama submissions of /v/programming. Until then, if no one has a better idea, questions are allowed to stay here, with me making no promise that this will stay this way on the long run.
TL;DR: Right now these submissions are okay, but this may change if someone has a better idea how to handle this.
0 u/leixiaotie [OP] 05 Aug 2015 16:41
Easy, it's not like this question is being responded anyway.