The choice of column type is not necessarily a simple choice and depends on both the database you’re using and the requirements of your application.
Depending on your particular usage scenario, storing binary data in the database can cause big performance problems. Active Record doesn’t generally exclude any columns when it loads objects from the database, and putting large binary attributes on commonly used models will increase the load on your database server significantly. If you must put binary content in a commonly-used class, take advantage of the select method to only bring back the columns you need.
The way that boolean values are stored varies from database to database. Some use 1 and 0 integer values to represent true and false, respectively. Others use characters such as T and F. Rails handles the mapping between Ruby’s true and false very well, so you don’t need to worry about the underlying scheme yourself. Setting attributes directly to database values such as 1 or F may work correctly, but is considered an anti-pattern.
:datetime and :timestamp
The Ruby class that Rails maps to datetime and timestamp columns is Time. In 32-bit environments, Time doesn’t work for dates before 1902. Ruby’s DateTime class does work with year values prior to 1902, and Rails falls back to using it if necessary. It doesn’t use DateTime to begin for performance reasons. Under the covers, Time is implemented in C and is very fast, whereas DateTime is written in pure Ruby and is comparatively slow.
It’s very, very rare that you want to use a :time datatype; perhaps if you’re modeling an alarm clock. Rails will read the contents of the database as hour, minute, and second values, into a Time object with dummy values for the year, month, and day.
Older versions of Rails (prior to 1.2) did not support the fixed-precision :decimal type and as a result many old Rails applications incorrectly used :float datatypes. Floating-point numbers are by nature imprecise, so it is important to choose :decimal instead of :float for most business-related applications.
If you’re using a float to store values which need to be precise, such as money, you’re a jackass.
Floating point calculations are done in binary rather than decimal, so rounding errors abound in
places you wouldn’t expect.
>>0.1+0.2 == 0.3
>>BigDecimal(‘0.1’) + BigDecimal(‘0.2’) == BigDecimal(‘0.3’)
Don’t use floats to store currency values, or more accurately, any type of data that needs fixed precision. Since floating-point numbers are pretty much approxi mations, any single representation of a number as a float is probably okay. However, once you start doing mathematical operations or comparisons with float values, it is ridiculously easy to introduce difficult to diagnose bugs into your application.
:integer and :string
There aren’t many gotchas that I can think of when it comes to integers and strings. They are the basic data building blocks of your application,
and many Rails developers leave off the size specification, which results in the default maximum sizes of 11 digits and 255 characters, respectively. You should keep in mind that you won’t get an error if you try to store values that exceed the maximum size defined for the database column, which again, is 255 characters by default. Your string will simply get truncated. Use validations to make sure that user-entered data does not exceed the maximum size allowed.
There have been reports of text fields slowing down query performance on some databases, enough to be a consideration for applications that need to scale to high loads. If you must use a text column in a performance-critical application, put it in a separate table.