Postgresql – Units of measurement in the database

database-designpostgresql

I have a table of products that can be measured by weight and size, and the user can enter which unit he wants to use for the measurement of any product. In the future he might need to see these measurements converted to different units.

I've looked up existing questions about this, and imo they had some complicated solutions using a table for the units and another table for converting from unit to unit, but I don't think I need all that.

My idea so far is to always store the measurement in the smallest unit I'll use (millimeter for distance and milligrams for weight) alongside with a column that tells in which unit this measurement should be displayed, that way I can even make calculations in the db queries, and in the GraphQL API layer or event in the client I'd convert the measurement the desirable unit.

What are the pitfalls to this approach?

Best Answer

The problem is that you should not allow free-form values for the units, but have a second table of units and reference that with a foreign key:

CREATE TABLE unit (
   unit_id bigint PRIMARY KEY,
   name text NOT NULL
);

CREATE TABLE measurement (
   measurement_id bigint PRIMARY KEY,
   unit_id bigint NOT NULL REFERENCES unit,
   value double precision NOT NULL
);

That guarantees that you can only store existing units in your measurement table, which is good for data quality (and it is not much extra effort).

Otherwise you can end up with entries like cm, centimeter, centimeters and Zentimeter all meaning the same thing.

Related Question