货币兑换表设计mysql,数据库设计：多币种账户

In your accounting software how would you design your accounts database for COA

Design 1) 1 account can hold 1 currency only

Design 2) 1 account can hold multi currency, just filter the transaction by CurrencyId (USD, GBP etc..)

Design 1) 1 account can hold 1 currency only

ACCOUNT

Id

AccountNumber

CurrencyCode

...

TRANSACTION

Id

AccountId

Amount

...

Design 2) 1 account can hold multi currency

ACCOUNT

Id

AccountNumber

...

TRANSACTION

Id

AccountId

Amount

CurrencyCode

....

解决方案

In the first place, I wouldn't design accounting software. I'd buy it. Accounting is one of the fields where you need domain experts (that is, accountants) helping to develop the software.

In the second place, if you're required to accept multiple currencies, then you have to store the type of currency along with the value. In a system like that, a debit of 30 is meaningless. Only debits of 30 USD or 30 EUR are meaningful.

But storage is the simplest part of your problem. How are you going to add up transactions involving multiple currencies? There's more than one way to do that, there's probably more than one "right" answer, and your accountants will probably come to blows over which way is the "right" way.