Different strategies for storing currency (MySQL)

Storing currency values in a database might seem like a trivial task, but it can become complicated due to issues like precision and rounding errors. This article will discuss several strategies for storing currency or money in a MySQL database, along with their pros and cons.

DECIMAL Data Type

The most recommended way to store currency is by using the DECIMAL data type. DECIMAL can store exact numeric data values, unlike FLOAT or DOUBLE, which are approximate numeric data types.

Here is an example of how to define a DECIMAL field for storing currency:

CREATE TABLE products (
    id INT AUTO_INCREMENT,
    price DECIMAL(19,4),
    PRIMARY KEY(id)
);

In this example, DECIMAL(19,4) means that the price field will store numbers that have up to 19 digits, including 4 digits after the decimal point.

Advantages:

Accurate to the last penny (or even beyond, depending on your specified decimal places), making it perfect for financial calculations. It is sometimes the guidelines of government financial bodies to store to 4 decimal places, even for currencies with only 2 decimal places. This is to increase precision when calculating tax.

Disadvantages:

• DECIMAL columns take more storage space compared to FLOAT or DOUBLE.
• Math operations with DECIMAL columns are slower compared to FLOAT or DOUBLE.

FLOAT or DOUBLE Data Types

Another way to store currency is by using FLOAT or DOUBLE data types, which can store approximate numeric data values.

CREATE TABLE products (
    id INT AUTO_INCREMENT,
    price FLOAT,
    PRIMARY KEY(id)
);

Advantages:

• FLOAT and DOUBLE columns take less space compared to DECIMAL.
• Math operations with FLOAT or DOUBLE are faster compared to DECIMAL.

Disadvantages:

• FLOAT and DOUBLE cannot store exact values, which can lead to rounding errors. This makes them a poor choice for financial calculations where precision is essential.

Storing Cents Instead of Dollars

Another strategy is to store the money value in cents (or the smallest unit of your currency) as an INTEGER.

CREATE TABLE products (
    id INT AUTO_INCREMENT,
    price INT,
    PRIMARY KEY(id)
);

In this case, if the price of a product is $19.99, you would store 1999 in the price column.

Advantages:

• Since you are storing an integer, the calculations will be exact, and you will not have to worry about rounding errors.

Disadvantages:

• This approach requires you to manually convert between dollars (or your main currency unit) and cents in your application logic.

Choosing the right method to store currency values in your MySQL database depends on your specific use case. If exactness is critical, as it usually is in financial applications, DECIMAL is often the best choice despite its higher storage costs and slower calculations. FLOAT and DOUBLE could be used for applications where the speed of calculations is more important and a minor loss of precision can be tolerated. Storing money as cents in an INTEGER field is another approach that ensures exact calculations and could be convenient for certain applications.