Should a value always be stored in a database using a specific unit (e.g. meters) or should the unit be settable in an additional field?

Question

Should a value always be stored in a database using a specific unit (e.g. meters) or should the unit be settable using an additional field or table?

Example 1: Unit defined as part of the field

buildings
-----------------------------
building_id       INT
date_built_utc    DATE
reported_area_m   DOUBLE

Example 2: Unit defined in another field

buildings
-----------------------------
building_id          INT
date_built           DATE
date_built_unit      VARCHAR(50)
reported_area        DOUBLE
reported_area_unit   VARCHAR(50)

I am strongly leaning towards Example 1 because reporting on the values would be easier ( and less error prone ) if there is one standard unit for storing the data. The application layer can easily handle any conversions between units if the need arises.

Which method would be preferred and why?

Answer 1

First, don't store information inside a field name, I don't think it's best practice

I would create a lookup table and do

 buildings
 -----------------------------
 building_id       INT
 date_built        DATE
 date_unit         INT
 reported_area     DOUBLE
 reported_unit     INT

Why lookup table, very simple.

Why would you want to duplicate 1000x meters while you can just use the number 1

Answer 2

I think allowing more than one unit would introduce a lot of confusion and maintenance headaches. Can you imagine someone reading 100 and thinking meters rather than feet? That's what caused the loss of a NASA orbiter, after all.

EDIT: What I mean is, by all means, if you can force a unit, do it. This will prevent any possible confusion over what the unit is.

Answer 3

Beware of underflow and overflow problems with using a single unit: don't store square picometers in your reported_area_m DOUBLE. Be sure to pick a reasonable base unit for the range of values you'll be storing.

Answer 4

It depends on how precise your measurements are, and how frequently users will want to work with values in different units. Either way, your code will need to be able to convert back and forth between all the units that your users want (e.g. m^2, ft^2, acre, etc.). In scientific datasets that I work with at work, we always transform into common units before saving to the database. The only case where we wouldn't do that is if the measurements were highly precise (e.g. size of the Earth to the nearest millimeter) and the conversion could introduce rounding error. For your case (meters vs. feet, for sizes up to a few thousand meters or feet, presumably?) rounding error is no big deal and converting between them is a fast linear operation, so I'd expect storing everything in meters would be sufficient.

Either way, you will need to document the heck out of whatever you do, so that nobody will treat the values as having the wrong units by mistake.

Answer 5

The answer to your question is another question: How would I find all the buildings with an area of at least x, but not more than y?

Answer 6

Example 1 is the way to go. If you needed to write a SQL query that used SUM or AVG on your reported_area field, think about how painful that would be if you needed to take the unit column into account.

Also, I don't think there's anything wrong with including the unit (meters, feet etc.) in the column name itself. This is a good idea, in fact, since the unit won't be indicated anywhere else in the database.

Answer 7

If you can live with rounding errors on the conversion, then using a common unit is surely the way to go, for all the reasons others have given.

That is a big potential "if" though, and must be considered.

Like so many questions, the answer is "well, it all depends ..."

Answer 8

None of us here have the same insight into your project the way you do, the best answer in this (and so many other cases) is - "It Depends", rarely are there any cut & dry, universally accepted answers to questions like this. You have to make your choice based upon what you're objective is and what is reasonable to do.

Polling the community is a great way to find out what your options are and what pitfalls could lie ahead should you choose a particular course of action, but definitive answers are probably beyond our reach.

As several people here have pointed out if you go down the road of storing multiple measurement types then you have the added responsibility of keeping track of how to convert between them and if you can accept rounding errors when converting between those values.

Storing everything in a standard unit is certainly desirable from a maintenance standpoint because it makes things simpler on both the front & back ends - should you need to represent the data differently you can always convert it when drawing it out from the database or convert it before it goes in...But does it meet your users requirements?

Answer 9

I like to store the units in the name of the column:

FileSizeInBytes
TimeSpanInSeconds
WidthInCentimeters

etc