Programming

How to Store Money in Databases and Why It's Not as Simple as It Seems

A comprehensive guide to storing monetary values in databases and APIs, covering ISO 4217, the pitfalls of floating-point types, and comparing integer minor units, decimal base units, and string representations as used by Stripe, PayPal, Visa, and others.

Storing money in databases

Storing money is something that only seems simple at first glance, but in practice contains a multitude of pitfalls.

The International Standard ISO 4217

The standard defines currency codes and their minor units. For example, the Russian ruble is coded as RUB (643) with 2 decimal places.

Currency Peculiarities

  • The Japanese yen has no fractional parts
  • The Jordanian dinar is divided into 1000 fils (0.001 JOD)
  • Cryptocurrencies have up to 18 decimal places
  • The currencies of Mauritania and Madagascar don't use the decimal system

Storage Requirements

  • Store amounts together with a reference to the currency specification
  • Include the minimum accounting unit
  • Ensure precision for all supported currencies
  • Add extra precision for internal operations

Data Types: What to Avoid

Float and Double -- "never use floating-point types for storing monetary amounts." The problem: 0.1 + 0.2 does not equal 0.3 in the binary system.

Floating point problem

MONEY type -- tied to locale, has fixed precision (2-4 decimal places).

Storage Approaches

1. Integer with Minor Units

Used by Stripe, Adyen, Klarna, MasterCard. The principle: $10.95 becomes 1095 cents.

BigInt -- for large numbers and cryptocurrencies:

  • JavaScript: built-in BigInt
  • Python: built-in int
  • Java: BigInteger
  • Go: big.Int
  • PHP: BCMath or GMP

In databases: DECIMAL(38,0)

Int64 -- limitations:

  • Insufficient for cryptocurrencies
  • Problems with JavaScript (Float64 has 52 mantissa bits)
  • PHP on 32-bit systems doesn't support it

2. Decimal Base Units

Used by Visa, PayPal. Stores $10.95 as 10.95.

Advantages:

  • Intuitive representation
  • Precision changes without data recalculation
  • Serialization naturally includes precision information

3. String Base Units

Used by Google Wallet, Amazon Payments, PayPal. The amount is passed as "10.95".

Advantages:

  • Universal compatibility (including JavaScript)
  • Self-documenting API
  • No overflow issues

Disadvantages:

  • Slower than integer arithmetic
  • Requires validation
  • More memory usage
Comparison table

Payment System Practices

Minor Units (Integer): Stripe, Adyen, Klarna, MasterCard

Base Units (String): Braintree, Google Wallet, PayPal, Amazon Payments, The Currency Cloud, 2checkout, GoCardless, Paynova, Rogers Catalyst, WePay

Base Units (Decimal): Dwolla, Venmo, Intuit

Usage Recommendations

  • MVP: String Base Units or Decimal (fewer pitfalls)
  • E-commerce: String Base Units (convenient for JavaScript)
  • Banking: Decimal (meets regulatory requirements)
  • High-performance systems: Integer minor units
  • Cryptocurrencies: BigInt minor units
  • Public APIs: String Base Units
  • Microservices: BigInt for internal calculations, String for external interfaces

Further reading