Database Design: Multi-Currency Accounts

Question

In your accounting software, how do you create a database of your accounts for COA

Design 1) 1 account can contain only 1 currency

Design 2) 1 account can contain several currencies, just filter the transaction using CurrencyId (USD, GBP, etc.).

Design 1) 1 account can contain only 1 currency

ACCOUNT Id AccountNumber CurrencyCode ... TRANSACTION Id AccountId Amount ... 

Design 2) 1 account may contain several currencies

 ACCOUNT Id AccountNumber ... TRANSACTION Id AccountId Amount CurrencyCode .... 

Answer 1

Firstly, I would not develop accounting software. I would buy it. Accounting is one of the fields in which you need domain specialists (that is, accountants) to help develop software.

Secondly, if you need to accept multiple currencies, you need to save the type of currency along with the value. In such a system, a debit 30 does not make sense. Only debit transfers of $ 30 or € 30 make sense.

But storage is the easiest part of your problem. How are you going to stack transactions with multiple currencies? There is more than one way to do this, perhaps there is more than one “right” answer, and your accountants will probably come to blows in how this is the “right” way.

Answer 2

You can use existing services as a guide - for example, Paypal.

In Paypal for each account you need to assign the base currency for this account. Then, any transactions are converted to this currency before being applied to the account. The original amount, its currency and the conversion rate applicable at that time can also be stored together, but you will use the base currency to make the calculations.

Answer 3

As a general safety rule, always use a more flexible design. In this interconnected world, there will almost certainly be transactions in all currencies (with the possible exception of selling local car boots, although I am not sure about that). So, save the currency code in the transaction table.