[FIXED] how to store bitcoin and other currencies in mysql database

Issue

I try to make payment system. Customers can make purchases with bitcoin or other curriencies like (USD,EUR) i have a decimal amount column with (16,8), for bitcoin amounts it works normally, but when i try to put usd value for example 100 usd , it becomes 100.00000000 , my question is should store amount like this ? use same decimal column for bitcoin and other currencies? is it bad for performance when counting all records? or should i have multiple column for bitcoin decimal(16,8) and for other currencies decimal(10,2) , show me a way - please consider millions of records when you answering.

Solution

There are two aspects of performance -- speed and space. Speed is usually not a concern because fetching rows is far more costly than the effort to manipulate decimal/double/etc. Space can be a concern (and lead to slowdowns) if you are talking about a billion rows.

Rule of Thumb: DECIMAL(m,n) take about m/2 bytes. In the case of (16,8), it takes exactly 8 bytes.

DOUBLE takes 8 bytes; BIGINT: 8 bytes. Etc.

Don't use VARCHAR for numeric values, especially if you need to sort them.

FLOAT and DOUBLE incur an extra rounding (decimal to/from binary), leading to possible round-off errors, especially when adding up lots of numbers.

I don't know for sure, 8 decimal places is the official max needed for Bitcoin, and is more than enough for any other currency. 4 is the most I have heard of for a currently used currency. (Pounds/Shillings/Pence is no longer in use.)

DECIMAL(16,8) overflows at 100,000,000 dollars/euros/whatever. Make sure that is enough. DECIMAL(17,8) also takes 8 bytes, giving you a Billion max. So you may as well use 17 instead of 16.

Back to the question... There is no 'perfect' answer.

Answered By - Rick James

Answer Checked By - Mildred Charles (PHPFixing Admin)