Representation of dimensional units in a standard way

Question

Suppose you want to write to the database that something is 30 meters long, or 50 feet, or the temperature was 50 Kelvin, the speed was 50 kilometers per hour. How do you represent units?

To clarify, two points:

• any units, not predefined, clearly defined subsets of them.
• my question is rather related to the existence of an ontology of units. I took an example database because it was the first that crossed my mind, but scripts like representing a block in XML or JSON are equally likely.

Answer 1

One of the basic concepts of a relational database is that all values ​​in this column must be some kind of logically compatible data type. Formally, a column has exactly one single type, and any two values ​​in a type can be compared with each other in the equality predicate. This is a key part of type theory.

So, if the measurements are not comparable, i.e. length versus temperature, you should not store them in one column.

You might want to check out ISO 2955 , “Information Processing - Representation of SI and Other Units in Systems with Limited Character Sets.

Also see Joe Celko's SQL Programming Style , Chapter 4, Scales and Dimensions.

Answer 2

Relational theory claims that every relvar ("table") has a related predicate that determines the meaning of the tuples in it. This predicate should be part of the official database documentation, so no one who really manages the documentation can have any excuse for being "misinterpreted" (unless, of course, the documentation is incomplete).

By including the definition of units in this predicate (for example, “Human length ... is KNIFE”, “Measured temperature was ... KELVIN”, ...) reaches this completeness and avoids these rather ugly attributes (“columns”).

I don’t understand why “just storing numbers” (in a standard unit agreed by all users) would be “not easy”.

If foobaricity exists as a whole, and someone comes up with a new modular fluffy perception, then someone will first have to officially establish the correspondence between the quantities of foobaricity and the quantities of fluffyperception in any case, or anything that he states will / can be understood by someone whatever.

EDIT

I saw that this added: "I need to save information about the source block."

Nothing prevents you from doing this. Two additional columns (original quantity and original unit name) along with the "canonical" value. You can limit the “original device name” to as strong or weak as you want.

Answer 3

Do you have a specific reason to store quantities in different types of units instead of converting them to some "canonical" units (for example, the metric system)? When you insert data, you convert the input value to a canonical unit. And when reading data, you convert to any output block that you need.

This approach is easier in many ways than storing data in different units, but you lose information about the source block in which the data was specified.

Answer 4

I would include units in the column name (e.g. LengthInMeters, WeightInKilograms, AnnoyingnessInFishSlapsPerSecond, etc.) and then just store the numbers in the column.

Ideally, it would be nice to define the device as a (correct) column property, but I don’t know a single database that allows this. Since the block included in the column name is difficult for future developers to get confused about this.

I came across DB solutions that include a block in the second column, but since there is no standard way of representing units, it ends up being a text box with the values ​​"ft.", "Feet", "Feet", etc. , or FK to the table in which the possible units are stored (also text). In any case, executing SUM or AVG requests (or any calculations) becomes a nightmare, especially if you allow you to store values ​​with different units in one column.