Roshan

Storing Prices in Your Database: The Right Way to Avoid Costly Mistakes

Roshan's photo
Roshan
·

6 min read

When building an e-commerce platform, a financial application, or any system that handles monetary transactions, one of the most critical components is how you store and calculate prices. Choosing the wrong data type for prices can lead to subtle yet costly mistakes, such as overcharging customers, incorrect totals, or problems with financial reports.

In this blog, we'll explore the best practices for storing prices in your database, including why certain data types like FLOAT and DOUBLE can cause issues, how DECIMAL is the most reliable choice, and why storing prices as integers (in cents) is often an optimal solution. We’ll also review how these approaches work across different databases like MySQL, PostgreSQL, SQL Server, SQLite, and Oracle.

The Problem: Storing Prices with FLOAT or DOUBLE

Why FLOAT and DOUBLE are Risky for Prices?

FLOAT and DOUBLE are data types used for storing floating-point numbers. These types are commonly used for scientific calculations, where small inaccuracies are often acceptable. However, for financial systems where precision is paramount, these data types can cause serious issues.

Imprecision of Floating-Point Numbers

Both FLOAT and DOUBLE represent numbers in binary, which means they can't always store decimal values exactly. For example, when you try to store a price like $19.99, the actual stored value might not be exactly 19.99. Instead, it could be something like 19.990000000000002 due to floating-point representation, which introduces small errors.

This is problematic because even the smallest rounding error can accumulate, leading to significant discrepancies in financial calculations, especially when dealing with large amounts of money or performing many calculations.

Real-World Example:

Consider an online store where you're calculating the total price of multiple items, including tax.

Let's say you're using FLOAT for prices:

CREATE TABLE products_float (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(191) NOT NULL,
  selling_price FLOAT NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO products_float (name, selling_price) 
VALUES ('Item 1', 19.99), ('Item 2', 4.50);

Now, you perform a simple calculation to get the total price including tax:

SELECT SUM(selling_price) * 1.05 AS total_price_with_tax FROM products_float;

The result might be slightly off, showing something like $25.7145 instead of the expected $25.71, due to the imprecision of floating-point numbers. This might seem minor, but when multiplied over thousands of transactions, these errors can add up and affect your bottom line.

The Right Approach: Using DECIMAL for Accuracy

Why DECIMAL is the Best Choice for Prices?

To avoid the issues introduced by FLOAT and DOUBLE, the best approach for storing prices is to use DECIMAL. DECIMAL (or NUMERIC in some databases) is a fixed-point data type that stores numbers exactly as they are, without any rounding errors.

  • Exact Precision: DECIMAL stores values exactly as entered, ensuring no rounding errors or precision loss.

  • Control Over Scale: You can specify both the total number of digits (precision) and the number of digits after the decimal point (scale). For example, DECIMAL(10, 2) ensures that you can store numbers up to 10 digits long, with 2 digits after the decimal point.

Example: Storing Prices with DECIMAL

Here’s how you can store prices using DECIMAL:

CREATE TABLE products_decimal (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(191) NOT NULL,
  selling_price DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (id)
);

Now, when you store and calculate prices, you'll avoid the rounding errors caused by FLOAT:

INSERT INTO products_decimal (name, selling_price) 
VALUES ('Item 1', 19.99), ('Item 2', 4.50);
SELECT SUM(selling_price) * 1.05 AS total_price_with_tax FROM products_decimal;

With DECIMAL, you can be confident that the calculations will always return the correct result, such as $25.71.

Alternative Solution: Storing Prices as Integers

Why Use Integers for Prices?

Another common practice is to store prices as integers, using the smallest unit of currency such as cents for financial transactions. This approach eliminates the need for decimal precision entirely and avoids rounding errors.

Benefits of Using Integers:

  • No Floating-Point Issues: Storing prices as integers (e.g., 1999 cents instead of 19.99 dollars) avoids any floating-point errors.

  • Performance Gains: Integer operations are generally faster than decimal or floating-point operations, which can be important in high-performance or high-traffic systems.

Example: Storing Prices as Cents

Here’s how you can store prices in cents using integers:

CREATE TABLE products_integer (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(191) NOT NULL,
  selling_price_cents INT UNSIGNED NOT NULL,
  PRIMARY KEY (id)
);

When inserting product prices, convert the dollars into cents:

INSERT INTO products_integer (name, selling_price_cents) 
VALUES ('Item 1', 1999), ('Item 2', 450);  -- 19.99 and 4.50 in cents

For calculations, keep everything in cents, and convert back to dollars when displaying results:

SELECT SUM(selling_price_cents) * 1.05 AS total_price_with_tax_cents FROM products_integer;

To display the total in dollars

SELECT SUM(selling_price_cents) / 100.0 AS total_price_in_dollars FROM products_integer;

This approach ensures precision and high performance, especially in systems with large numbers of transactions.

Database-Specific Considerations

Different databases offer various ways to store and handle decimal numbers. Here’s a quick rundown of how each major database deals with monetary values:

MySQL

  • Preferred Data Type: DECIMAL(p, s)

  • Syntax Example:

    •   CREATE TABLE products (
    selling_price DECIMAL(10, 2) NOT NULL
  );

PostgreSQL

  • Preferred Data Type: NUMERIC(p, s) or DECIMAL(p, s)

  • Syntax Example:

    •   CREATE TABLE products (
    selling_price NUMERIC(10, 2) NOT NULL
  );

SQL Server

  • Preferred Data Type: DECIMAL(p, s) or MONEY

  • Syntax Example:

    •   CREATE TABLE products (
    selling_price DECIMAL(10, 2) NOT NULL
  );

SQLite

  • Preferred Data Type: NUMERIC or INTEGER (for storing cents)

  • Syntax Example:

    •   CREATE TABLE products (
    selling_price NUMERIC NOT NULL
  );

Oracle

  • Preferred Data Type: NUMBER(p, s)

  • Syntax Example:

    •   CREATE TABLE products (
    selling_price NUMBER(10, 2) NOT NULL
  );

In all these databases, you should avoid FLOAT and DOUBLE for monetary values, as they are not designed for exact precision and can cause rounding errors.

Conclusion: Best Practices for Storing Prices

Storing prices correctly in your database is crucial for the integrity of financial calculations in your application. Here's a quick summary of the best practices:

  • Avoid FLOAT and DOUBLE: These data types introduce rounding errors and imprecision, which are unacceptable for monetary values.

  • Use DECIMAL or NUMERIC: These are designed to store exact values, with control over precision and scale, making them perfect for handling prices.

  • Consider Storing Prices as Integers: For improved performance and precision, especially in high-traffic systems, storing prices as integers (in the smallest unit of currency) can be an ideal solution.

By following these best practices, you'll ensure financial accuracy, scalability, and optimal performance in your system protecting both your business and your customers.

Final Thoughts

Implementing the correct data types for prices in your database is a small but critical detail in building robust financial systems. Understanding the consequences of bad practices, like using floating-point numbers for prices, and applying the right solution (like DECIMAL or integers) will help avoid costly errors, ensure the accuracy of your financial calculations, and ultimately deliver a better experience for your users.

Databasesdatabase designdatabasemanagementdatabase