Practical guide

How to store money and prices correctly in a database

Store an amount exactly, always keep its currency explicit, and round at a predefined boundary.

20 minutes · Databases

In short

Money is not an ordinary float

Binary floating point cannot represent many decimal amounts exactly. Small errors surface in sums, taxes, and comparisons, so it is not suitable for money.

Two common safe representations are an exact decimal such as PostgreSQL NUMERIC, or an integer in the smallest used unit. In both cases, the value also needs a currency and a rounding policy.

Prepare

Decisions to make first

The database type follows business rules. Define precision, currencies, and the rounding point first.

Supported currencies and decimal places used for accounting, prices, and intermediate calculations.
A rounding rule for unit price, tax, document line, and final total.
A decision to store historical documents as immutable snapshots instead of recalculating them from the current catalog.
Maximum amount and calculation range used to choose decimal precision and scale or a BIGINT range.

Steps 1 to 3

Introduce an exact money model

Choose one representation per context and do not mix decimal strings, floats, and minor units without an explicit conversion boundary.

1. Choose NUMERIC or minor units

NUMERIC(p, s) stores decimal values exactly. Choose precision and scale from the maximum amount and required intermediate calculations.
A BIGINT in minor units simplifies sums and comparisons. Not every currency uses two decimal places, so never hard-code conversion as times one hundred.
Do not use PostgreSQL money as a universal domain model. Its input and output depend on locale and the type does not carry a currency code.
NUMERIC is often practical for catalog prices and tax calculations; integer minor amounts can suit settled payments. Document the choice.

amount NUMERIC(19, 4) NOT NULL, currency CHAR(3) NOT NULL

Official PostgreSQL numeric type documentation

2. Transfer amounts without floats

Accept a decimal amount from an API or form as a validated string. Do not pass it through a PHP float before storing it in NUMERIC.
Use a decimal or money library with an explicit rounding mode. The string 19.90 must remain the exact value 19.90.
Keep amount and ISO currency code together in a value object. Do not allow EUR and CZK to be added without an explicit exchange rate.
Do not send formatted values with commas, currency symbols, or thousands separators to the database. Format only at the output boundary.

new MoneyAmount('19.90', 'CZK')

Official PHP floating-point precision documentation

3. Define one rounding boundary

Name the rounding mode and where it applies. Rounding every intermediate result and rounding only at the end produce different totals.
For an invoice, store unit price, quantity, rate, tax base, tax, total, and currency as approved at issue time.
Use constraints for currency format and valid range. A negative value may be correct for a credit note, so derive rules from column meaning.
Share the same examples and rounding mode between backend, accounting export, and tests. A frontend preview must not be the only calculation.

rounding: half-up; scale: 2; apply: invoice line tax

Official PHP rounding documentation

Step 4

Test precision and boundaries

Use values where representation and rounding actually break, not only whole units.

Add problematic tenths

Verify the exact result of 0.10 + 0.20 and preservation of trailing scale according to the model.
```
SELECT 0.10::numeric + 0.20::numeric;
```
Test ties and negative amounts

Add exact rounding ties, a credit note, zero, and the maximum permitted value.
```
php bin/phpunit --filter Money
```
Compare a complete document

Lines, taxes, and total must match an approved accounting example and remain identical after a database round trip.

When it goes wrong

Common mistakes

19.90 became an imprecise float in transit

Find the float conversion in the request, DTO, or serialization. Carry decimals as strings or a money value object to the database driver.

Invoice line sums do not match the total

Components use a different boundary or mode. Define whether lines, tax, or only the total is rounded and store the approved snapshot.

An integer amount is off by one hundred for a currency

The conversion assumes two decimal places. Use metadata for the particular currency and an explicit major-to-minor boundary.

The database money type formats differently on another server

It is locale-dependent. Use NUMERIC or BIGINT with a separate currency code for a portable model.

Done

Amounts are exact and their meaning is complete.

The database now stores an exact value, its currency, and the agreed business rounding. Use the same model from input through calculation to the immutable document.

Read data type in the glossary All guides