Sql-server – Extending fiat transaction log to support cryptocurrency

database-designsql serversql-server-2012

Let's assuming that I have an existing database that retaings fiat currency transaction as described below with decimal (18, 4).

Now, I would like to record bitcoin, ethereum and erc20 token transactions.

Transaction
------------------
TransactionID
CurrencyID
UserID
DebitAmount  decimal(18, 4)
CreditAmount decimal(18, 4)

The decimal precision of cryptocurrency is suggested as follows:

 decimal (16, 8)  -- bitcoin
 decimal (30, 18) -- ethereum / erc20 token

I am thinking of just:

  1. Treating bitcoin, ethereum, erc20 token as new currency in the Currency table.

  2. Changing the DebitAmount and CreditAmount directly to decimal(30, 18).

  3. Applying decimal(30, 18) data type changes to all the tables that retain currency amount.

My questions are:

  1. Any possible impact to original currency amount conversion that I need to look after for when I increase the precision (eg. currency rate change DebitAmount * 1.023)?

  2. Am I doing this in the "correct" way?

  3. Is there any other better approach/alternative to this?

Best Answer

Any possible impact to original currency amount conversion that i need to look after for when i increase the precision?

Yes.

Carefully review your calculations against the table here: Precision, scale, and length, because you can have loss of precision when multiplying decimals:

The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, it's reduced to 38, and the corresponding scale is reduced to try to prevent truncating the integral part of a result.

Related Question