Codify a measure in a database field name

Question

Hello,

I've got a question concerning fields in databases wich are measures that might be displayed in different units but are stored only in one, such as "height", for example.

Where should the "pattern unit" be stated?. Of course, in the documentation, etc... But we all know nobody reads the documentation and that self-docummented things are preferrable.

From a practical point of view, what do you think of coding it in the database field (such as height_cm for example)?.

I find this weird at a first look, but I find it practical to avoid any mistakes when different people deal with the database directly and the "pattern unit" will never change.

What do you think?.

Regards.

Answer 1

What's weird about height_cm? Looks good to me.

Sometimes you see measures and units in two separate fields, which is much more painful.

As long as you know the units aren't going to change, I think height_cm is a good way to deal with it.

Answer 2

Most databases support comments on columns. For example in Postgres you could set a comment like this:

COMMENT ON COLUMN my_table.my_column IS 'cm';

Storing the unit name this way means your database is self-documenting. I would also strongly recommend using standard scientific units (i.e. the metric system).

Answer 3

I agree, nothing wrong with adding the unit to the field name.

The only thing I'd say is to make the naming convention consistent across your database - i.e. not "height_cm" and "mm_width"!

Answer 4

Be wary about measures that may change like currencies. In many cases it is not practical rename database field when it's measure changes.

It is rather silly to have a field called amount_mk which used to contain money amount in marks but currently actually contains money amount in euros.