Currency Code Best Practices in Database Design

Storing Currency Data in Databases

How you store currency codes and monetary amounts in your database has long-term implications for correctness, performance, and flexibility. Here are proven best practices.

Currency Code Column

-- RECOMMENDED: CHAR(3) for ISO 4217 codes
currency CHAR(3) NOT NULL DEFAULT 'USD'

-- Also acceptable: VARCHAR(3) if your DB handles it efficiently
currency VARCHAR(3) NOT NULL DEFAULT 'USD'

-- NOT recommended: ENUM (inflexible, hard to add new currencies)
currency ENUM('USD', 'EUR', 'GBP') -- Adding a currency requires ALTER TABLE

Why CHAR(3)?

• ISO 4217 codes are always exactly 3 characters
• Fixed-width is more efficient for indexing and comparison
• No need for the flexibility of VARCHAR

Monetary Amount Column

-- RECOMMENDED: Store amounts in minor units as integers
amount BIGINT NOT NULL  -- Cents, yen, fils, etc.

-- Also acceptable: DECIMAL for human-readable storage
amount DECIMAL(19, 4) NOT NULL  -- Supports up to 15 integer + 4 decimal digits

-- NOT recommended: FLOAT or DOUBLE
amount FLOAT  -- Precision loss! 0.1 + 0.2 != 0.3

Complete Schema Example

CREATE TABLE transactions (
  id              BIGINT PRIMARY KEY AUTO_INCREMENT,
  amount          BIGINT NOT NULL,          -- Minor units
  currency        CHAR(3) NOT NULL,         -- ISO 4217
  amount_decimal  DECIMAL(19, 4) GENERATED ALWAYS AS (
    amount / POWER(10, CASE currency
      WHEN 'JPY' THEN 0
      WHEN 'KWD' THEN 3
      WHEN 'BHD' THEN 3
      ELSE 2
    END)
  ) VIRTUAL,                                -- Computed column for display
  exchange_rate   DECIMAL(18, 8),           -- Rate at time of transaction
  base_amount     BIGINT,                   -- Equivalent in base currency
  base_currency   CHAR(3) DEFAULT 'USD',
  created_at      TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_transactions_currency ON transactions(currency);

PostgreSQL Money Type?

PostgreSQL has a built-in money type, but it is not recommended:

• Tied to the database locale setting
• No built-in multi-currency support
• Precision is fixed (2 decimals) — fails for JPY, KWD
• Use BIGINT or NUMERIC instead

Key Principles

1. Never use floating-point for monetary amounts
2. Always store the currency code alongside the amount
3. Use integers (minor units) when possible for simplicity and correctness
4. Index the currency column if you query by currency frequently
5. Store the exchange rate used at the time of any conversion
6. Use CHAR(3) for currency codes, not TEXT or ENUM
7. Add CHECK constraints to validate currency codes if your DB supports it

Migration Considerations

When adding multi-currency support to an existing single-currency system:

-- Step 1: Add currency column with default
ALTER TABLE orders ADD COLUMN currency CHAR(3) NOT NULL DEFAULT 'USD';

-- Step 2: Convert existing amounts to minor units (if stored as decimals)
UPDATE orders SET amount_cents = ROUND(amount * 100);

-- Step 3: Add new minor-unit column, then swap
ALTER TABLE orders ADD COLUMN amount_minor BIGINT;
UPDATE orders SET amount_minor = ROUND(amount * 100);