MySQL field type for weight and height data

Question

What is the adequate MySQL field type for weight (in kilograms) and height (meters) data?

Solution

That all depends. There are advantages and disadvantages to the different numeric types:

If you absolutely need the height/weight to be exactly a certain number of decimal places and not subject to the issues that floating point numbers can cause, use decimal() (decimal takes two parameters, the amount number of digits > 1 and the number of digits < 1, so decimal(5,2) would be able to store numbers as low as 0.01 and as high as 999.99). Fixed precision math is a bit slower, and it's more costly to store them because of the algorithms involved in doing it. It's absolutely mandatory to store currency values or any value you intend to do math with involving currency as a numeric(). If you're, for instance, paying people per pound or per meter for a baby, it would need to be numeric().

If you're willing to accept the possibility that some numeric values will be less precise than others use either FLOAT or DOUBLE depending on the amount of precision and the size of the numbers you're storing. Floating points are approximations based on the way computers do math, we use decimal points to mean out of powers of 10, computers do math in base2, the two are not directly comparable, so to get around that algorithms were developed for cases in which 100% precision is not required, but more speed is necessary that cannot be achieved by modeling it using collections of integers.

For more info:

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html http://dev.mysql.com/doc/refman/5.0/en/fixed-point-types.html http://dev.mysql.com/doc/refman/5.0/en/floating-point-types.html