Search code examples
sql-serverdatabasedomain-driven-designcurrencyfinance

How to manage multiple currencies in the database

If I have different multiple payroll disbursements based on the company location, the employee contract type and other criteria: So

Some have their salaries divided into parts the first in USD and the second in Local cur.

Ex: Employee 343 receive his salary as following: 200 USD & 3000 EGP (The local currency).
EX: Employee 672 receive his salary as following: 200 USD & 200 EUR (The local currency).

Some have their salaries just in one currency (the local).

Ex: Employee 532 receive his salary as following: 5000 EGP.

So We have multiple currencies for the salaries of the company based on the location and other business rules.

What 's the most proper way to save the salaries of the employees with these variations in DB so that It's easy to retrieve, process and scale.

If I use one table so I will have many columns and I have to add new columns every time a new branch for the company is initiated. Like this:

Sal_USD  | Sal_EGP | Sal_Eur

Is there an elegant way to handle this?

Solution

  • The correct way is always to support an infinite numbers of connections. So create a table with salary an another with currency.

    I think 3tables would do the jobb

    Employee (the one you have now)
- EmployeeID

Salary
 - SalaryID
 - EmployeeID
 - CurrencyID
 - Wage
 - Exchangerate ?

CurrencyID
 - CurrencyID
 - Name (USD/GDP/EUR....)

    Then you simply join them in.