Currency exchange in database

Question

I am designing a relational database that includes currency exchange rates. My first intuition was to have a currency table that lists all the currencies and holding as attributes the exchange rate for every other currency.

Ex: Currency Table

id INT
base_currency_code VARCHAR
date DATE
USD DECIMAL
GBP DECIMAL
EUR DECIMAL

I've done a search online on how currency exchange tables are implemented in databases, and everywhere I've looked instead has a table currency_exchange with columns base_currency, non_base_currency (to currency), rate, and date.

Since I can't find an implementation similar to mine and since one post I read said the other implementation is how it's done at the financial company he works at, I assume that the other is superior to mine!

I just don't understand why. Could someone please explain why the other is better?

Your table is not normalized. What happens if you want to add a new currency? Add another column? No, a table structure should be fixed and not evolve with the data. — juergen d, Jan 14 '18 at 18:36
@StevenAnderson first normal form, as pointed out if you need to support new currency you need to add new column. Anything that require you to change table structure is a bad design. — BabyishTank, Jul 29 '22 at 19:22

score 6 · Answer 1 · answered Jan 14 '18 at 19:09

You want tables like something this:

create table Currencies (
    CurrencyId int primary key,
    ISO_Code varchar(3),
    Name varchar(255)
);

create table CurrencyExchangerate (
    CurrencyExchangerateId int primary key,
    FromCurrencyId int not null references Currencies(CurrencyId),
    ToCurrencyId int not null references Currencies(CurrencyId),
    RateDate date not null,
    ExchangeRate decimal(20, 10),
    constraint unq_currencyexchangerate_3 unique (FromCurrencyId, ToCurrencyId, RateDate)
);

Currency exchange in database

1 Answers1