In a previous project, I noticed that the price field was being stored as an int, rather than as a float. This is done by multiplying the actual value by 100, the reason being was to avoid running into floating point problems.
Is this a good practice that I should follow or is it unnecessary and only makes the data less transparent?
While it would save you float-related issues, having prices saved as integers might lead to a problem where you end up charging 100 times the price to a customer. It could also confuse other programmers.
I have seen both solution used successfully on medium-size ecommerce websites, but my preference goes to using floats.
Floating point errors could cause you problems if you are multiplying large numbers. In general, financial calculations should never be done with floating point numbers where possible.
Interesting question.
I wouldn't actually choose float in the mysql environment. Too many problems in the past with precision with that datatype.
To me, the choice would be between int and decimal(18,4).
I've seen real world examples integers used to represent floating point values. The internals of JD Edwards datatables all do this. Quantities are typically divided by 10000. While I'm sure it's faster and smaller in-table, it just means that we're always having to CAST the ints to a decimal value if we want to do anything with them, especially division.
From a programming perspective, I'd always prefer to work with decimal for price ( or money in RDBMSs that support it ).