Decimal numbers in Rails and MySQL

When you’re dealing with non-whole numbers in your application, there are a couple of considerations for storing them in the database.

The trouble with float

Most languages and databases have great support for floating point numbers, which from a naive perspective, would mean “numbers with a decimal point.” The trick of it is that these representations aren’t exact, because fractional numbers can go on for infinity, which is more than we can say for your computer’s storage. This results in some subtle inaccuracies that you may never see, but if you do, I guarantee they’ll pop up at the worst possible time, and if you’re using floats to store money amounts, there really isn’t a good time for a problem to appear…

(By the way, for a whole lot of detail on floating point numbers I recommend What Every Computer Scientist Should Know About Floating-Point Numbers.)

There are many programmers out there who, when assigning data types, will follow a basic “whole numbers are integers, fractions are either floats or doubles” approach (a double is a float with greater precision, but I’ll wager that many programmers will just use one or the other as a matter of consistency without any justification.)

In many languages, there’s another option, which is an abstraction for a decimal representation. This type usually has a smaller range of digits, but will be more accurate within that range, which makes it much more suitable for things like currency calculations.

In a Rails application backed to MySql, data columns are mapped to the :decimal type in migrations:

    create_table :example do |t|
      t.decimal :amount
    end

Within the Rails app (for parsing strings, for example,) this would map to the BigDecimal type, so you can do things like @example.amount = BigDecimal(string_to_parse).

Gotcha.

The missing piece to the puzzle is the precision of the decimal within the database. By default, the code above will create a decimal field in MySQL with 10 digits, all of which are to the left of the decimal point. In other words, saving 15.37 to your model will show 15.37 within the app, but when you save it, it’ll become 15 both in the data store and when you reload it.

The way around this is to specify the precision and the scale in your migration. Let’s fix that earlier code – you can do this right in the create_table logic in the first place, but odds are you’ll forget at some point and have to correct it with a followup migration:

    change_column :example, :amount, :decimal, :precision => 16, :scale => 2

In this case, we’ve set the total number of digits (the precision) to 16, with 2 decimal places (the scale.) That’s enough to render a big dollar amount, but you can pick your sizes as per your needs – the MySQL docs give some guidelines to the storage requirements for different combinations.

Also, when picking your precision and scale, be aware that the scale (max decimal places) can’t be greater than the precisions (total digits.) That might seem obvious when written here, but without those translations in parentheses, it’s easy to get lazy and make the two values the same, which would leave you with a number that has to be less than 1.0, since all the space will be allocated to the right of the decimal place. Yeah, ask me how I know that…

Tagged as: arithmetic precision, data typing, decimal numbers, decimal points, decimals, floating point, floats, mysql, numbers, precision, rails