I want to store metric units in a database - what datatype should I use?

Question

I'd like to store height which would eventually be displayed in Imperial/US ( 6 ft 4 for example ) in a database which is currently MySQL ( which I plan to migrate to PostgreSQL ).

I'm wondering what unit is good for this type of storage, and what column type I should use? I'm thinking centimeters/metric system and some numeric column type, but would be great if someone can provide insight.

Answer 1

store it as centimetres using an int datatype. Note 1cm = 0.39370078740157477in

Answer 2

I think it mostly depends on what operations are to take place on the data. If it is just going to be stored and retrieved for display, then store it as text. If you need to do calculations like average and range, store it in a numerical type after reducing to a base unit of measure (e.g., inches, or hundredths of an inch). The choice of integer or floating-point type again depends on what calculations you need to perform.

The only reason to convert to metric in the db and then back to Imperial units for output would be if you need metric for some other reason, and one that is more important/frequent than generating output in Imperial. Otherwise all you are doing is introducing rounding errors in both directions.