Jun 2019

The best data type for currency or monetary values in databases is a 64-bit integer. In most relational databases the underlying type is bigint.

FLOAT and MONEY types can also store values but are not useful if you need to do any complex math or analysis. Floating point errors and rounding errors can quickly cause chaos and are not worth the tiny savings in data size. 64-bit integers are simple to use, compatible with every language, and have a smaller data size than strings or numerics.

The stored values should be in the smallest currency unit for the application (cents for a USD centric system) or an even smaller unit with a multiplier for more compatibility across currencies, especially with cryptocurrency values. I recommend microdollars where dollars are divided by 1 million.

For example: $5,123.45 can be stored as 5123450000 microdollars. This provides enough granularity for very precise values (like billing for ad impressions or API charges).


No comments posted.