Integer vs decimal db design

Rails
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.

–
Volker

2

On Sat, Jul 5, 2008 at 4:07 AM, [email protected]
[email protected] wrote:

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

–

Rails, RSpec and Life blog…

3

On Jul 4, 7:07 pm, “[email protected]”
[email protected] wrote:

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