The problem you are describing here is actually valid for all tasks where something has to be versioned - here: Exchange rates depending on date
I such cases I am usually adding a lookup table for currencies and store the currency_id next to the price.
Example
Table "invoicess"
- id
- status_id
with this field you could control whether the prices of this invoice should be recalculated when saving or should remain unchanged
- ...more fields...
Table "invoice_positions"
- id
- invoice_id [int fk referencing invoices table]
- position
- ...
- price [editable input field, decimal 10,2 for example]
- currency_id [int fk referencing pk of currencies table]
- rate [readonly, populated on insert or update with actual rate from currencies table]
- price_local [readonly, calculated on insert or update]
this is the calculated price in local currency as a result of price in foreign currency x quantity x currency-rate at a certain date
- ...more fields...
Table "currencies"
- id (1)
- name ("Euro")
- abbreviation ("EUR")
- symbol ("€")
- actual_rate (1.00000)
On insert/update, if price-calculation is required, you can calculate a "normalized" price in local currency (for example in EURO) by multiplying the price and the actual currency rate of the selected currency. The rate can be read from another table or for example from a financial webservice API.
Having the normalized price-column will help you for sums, for reporting etc. because the normalized price will already contain the correct currency rate at a certain date and not the actual currency rate which might be completely different when viewing the record later.
Extension for more flexibility
In case you need some kind of currency-rate history or if you'd like to import or edit the rates manually, I recommend having another table for currency-rates per date/period of time:
Table "currency_rates"
- id
- currency_id
- date_from
- date_till
- rate
Then, in "invoice_items" table next to curreny_id and price you should store the date. Having date and currency_id, you will be able to fetch the rate for a specific date and currency from "currency_rates" table. Having the rate for that day, you can calculate the normalized price event later.
The solution i have described here might look a bit complicated. but if you want to work with different currencies, you have to store the exchange rates somewhere. Either you have to store the exchange rate directly in addition to the price and/or in a separate lookup table.
From my experience, it makes sense to store the calculated price in local currency in addition to the price in foreign currency and in addition to the currency. This makes it easier in the following calculations.
