Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Tokyo Japan
    Posts
    26

    Question how to best design a "foreign currency" attribute???

    how would one best design a foreign currency value (DBMS-independent)?

    the idea that i have in mind would be the following:

    --------------

    TM_CURRENCY_MASTER table
    CurrencyID : integer
    CurrencyName : varchar
    CurrencyCountry (??) : varchar

    T_CURRENCY_MATRIX table
    CurrencyID_1 : integer
    CurrencyID_2 : integer
    ExchangeRate : decimal

    --------------

    the actual transaction table would then have the following fields (considered as a SET):

    ...
    etc
    ...
    CurrencyID : integer
    CurrencyAmount : decimal
    ...
    etc
    ...


    what do you people think?

    thanks!!!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sounds good

    i like the second table, with

    CurrencyID_1 : integer
    CurrencyID_2 : integer
    ExchangeRate : decimal

    this allows an exchange rate for CurrencyID_1 --> CurrencyID_2 that is different from the inverse rate for CurrencyID_2 --> CurrencyID_1

    you're probably want a time stamp somewhere, because rates do change frequently
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Yes, currency exchange rates change frequently. Worse yet, you can have more than one exchange rate in effect at the same time (for example, the rate from US Dollars to Euros may be different in Cairo and Tokyo at any given point in time! Because of that, for each exchange rate row you want both a begin and an end date (to allow multiple rates to be active at one time).

    -PatP

  4. #4
    Join Date
    Dec 2003
    Location
    Tokyo Japan
    Posts
    26
    thanks for the suggestion regarding a "start" date, and a "end" date... and of course, the "timestamp"...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •