Database Design – Creating a Table for Exchange Rates

database-design

I want to create a table in my database to store the exchange rates for currencies so I don't have to make an api call every time.

My table has the following structure:

from_currency
to_currency
rate

If I have 170 currencies the table will have 170 * 170 (~29.000) entries. This seems a bit much for me. Is this the normal way to store exchange rates?

Best Answer

There isn't really anything wrong with that design, its almost exactly what our production fact table looks like so you aren't far off. 29,000 rows isn't that many these days.

Here is the structure that we use in our master warehouse:

CREATE TABLE Fact.ExchangeRates
(
    TradingDate    DATE         NOT NULL, 
    SourceCurrency CHAR(3)      NOT NULL,
    TargetCurrency CHAR(3)      NOT NULL,
    ClosingRate    DECIMAL(9,2) NOT NULL,
    AverageRate    DECIMAL(9,2) NOT NULL
);

Then, we have a fact table in each of our client data warehouse's that is built from the master. The data warehouses are set to use a single base currency so we can eliminate the source currency:

CREATE TABLE Fact.ExchangeRates
(
    DateKey     DATE         NOT NULL REFERENCES Dimension."Date"(DateKey),
    CurrencyKey CHAR(3)      NOT NULL REFERENCES Dimension.Currency(CurrencyKey),
    ClosingRate DECIMAL(9,2) NOT NULL,
    AverageRate DECIMAL(9,2) NOT NULL
);
Related Question