integer vs decimal db design

rubyonrails-talk
1

Hi,

I ve got a table with 6 attributes which all have a two digit fractional part.

Now, I am wondering if it would be better to define them as decimals or as integers (dividing them by 100 in my app)....

Are there significant performance advantages for integers...?

There will be lots of records in that table and I will use these attributes for statistics.

Your help would be greatly appreciated.

2

I ve got a table with 6 attributes which all have a two digit fractional part. Now, I am wondering if it would be better to define them as decimals or as integers (dividing them by 100 in my app)....

If it is money, do the 100 divided by thing and store them as integers. Floating point numbers are always approximates and never an absolute.

Are there significant performance advantages for integers...?

Not really, but most databases handle integers faster than any other numeric type. Won't be noticable unless you are doing thousands of calls though.

There will be lots of records in that table and I will use these attributes for statistics.

In that case, you might need to store them as fractions, your call.

Regards

Mikel

3

I ve got a table with 6 attributes which all have a two digit fractional part.

Now, I am wondering if it would be better to define them as decimals or as integers (dividing them by 100 in my app)....

At the DB level, the decimal type is designed for this problem so would be the logical choice and would mean a little less work on your part so would be easier to manage. However, to use decimals in Ruby you'll need to use the BigDecimal class, I can't remember if Rails natively retrieves/stores decimals as BigDecimals. The class itself is initialised from a string representation and you'd need to make sure that at no point is the value converted to a float.

Are there significant performance advantages for integers...?

There will be lots of records in that table and I will use these attributes for statistics.

I believe that both the DB and Ruby store the number internally as a string of digits instead of normal binary representation. So you may expect a performance penalty but I'm afraid I don't know the magnitude of it, wouldn't be too hard to check with Ruby's benchmark lib.

Hope that helps,

Andrew