How should I present a cost field to the user, and store it in the database?

Question

Right now I have two fields for cost. One for dollars and one for cents. This works, but it is a bit ugly. It also doesn't allow the user to enter the term "free" or "no cost" if they want. But if I only have one field, I might have to make my parser a bit smarter. What do you think?

On the server side, I combine dollars and cents to store them as decimals in my database. Mainly so that I can gather statistics (cost averages, etc.) quickly.

Do you think it is better to store the cost as a string? Then whenever I actually use the cost for stats or other purposes, I would convert it to a decimal at that point. Or am I on the right track?

Answer 1

I generally store the cost as the lowest unit (pennies) and then convert it to whole dollars later.

So a cost of $4.50 gets stored as 450. Free items would be -1 pennies. You could store free things as 0 pennies as well, this gives you the flexibility to use 0 and -1 to mean two slightly different things (free vs no sale?).

It also makes it easier to support countries that don't use cents if you choose to go that route.

---

As for presenting the data entry field, I personally don't like it when I have to keep switching fields for tiny things (like when they break up phone numbers into 3 fields, or IP addresses into 4). I'd present one field, and let the users type the decimal point in themselves. That way, your users don't have to tab (or click, if they are unfamiliar with tab) to the next field.

Answer 2

Keep the whole cost as decimal. If it's free, then keep the cost as 0. In presentation if cost is zero - write "free" instead of 0.

Answer 3

Don't allow garbage to make it to your database. If you're expecting a dollar amount on a field, than make sure it's valid before it gets in there. This will allow you to report better on the data and allow simpler formatting on output.

I suggest making this a single field with validation on update or insert.

if field != SpecialFreeTag then

  try to convert to decimal
    if fail then report to user
    otherwise accept value

Use try parse or regular expressions to help with the validation.

Answer 4

There is a rule in database design that states that "atomic data" should not be split. By this rule a price, or cost is such an example of atomic data and therefore it should never be split among multiple columns just like you shouldn't split a phone number among multiple columns (unless you really have a very good reason for it - very rare)

Use a DECIMAL data type. Something like DECIMAL(8,3) should work and it's supported by all ANSI SQL compliant database products!

You can consult Joe Celko's "Thinking In Sets" book for a discussion of this topic. See section 1.6.2, pages 21-22.

EDIT - It seems from your question that you are also concerned with how to accept user's input in a form that resembles the price (xxxx.xx) - hence the two input boxes, for the whole dollars, and the pennies.

I recommend using a single input box and then doing input validation using Regular Expressions to match your format (i.e. something like [0-9]+(.[0-9]{1,3})? would probably work but could be improved). You could then parse the validated string to a Decimal type in your language, or just pass it as a string into your database - SQL will know how to cast it to a DECIMAL type.

Answer 5

I would store the cost as decimal with the scale being no less than 2 and maybe even 3-5. If something is bought in bulk the unit cost could easily include fractions of a cent. Free items have a cost of 0. If the cost is unknown then allow null values also.

Answer 6

Use cents, use 450 for $4.50 this will save you problems that are arising very often from the fact that floating point operations are not safe. Just try the following expression in irb: 0.4 - 0.3 == 0.1 will return false. All because of floating point representation innacuracies.

In my models I'm always using:

attr_accessor :price_with_cents
def price_with_cents
  self.price/100.00
end

def price\_with\_cents==(num)
  self.price = (num.to_f * 100.00).to_i
end

And the name of column is just price and integer type.

I don't have much experience with decimal columns and their representation in ruby (which can be float that is problematic as i've shown at the begining).