0

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?

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
4
  • Yea I agree I need a way to enforce this, though usually I'll use check constraints rather than another table to reference. So other than that is this pattern ok?
    – koi
    Mar 17, 2020 at 17:59
  • Yes, a check constraint is ok if you need the units in only one table. Otherwise you'd have to keep the check constraints on several tables the same, which is redundant and a pain. Moreover, an ALTER TABLE will need a short ACCESS EXCLUSIVE lock, which is more disruptive than adding a row to a table.
    – Laurenz Albe
    Mar 17, 2020 at 18:18
  • Why would I need a lock, can you give an example?
    – koi
    Mar 17, 2020 at 18:40
  • If you ever need to change the check constraints, because you need a new unit.
    – Laurenz Albe
    Mar 17, 2020 at 18:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.