standard solution for storing units of measure

Question

INTRODUCTION AND RELEVANT INFORMATION:

Let us say I have a table that stores height and weight of a person, something like this :

PersonTable < # ID int, Name varchar(50), Height double, Weight double >

Height is measured in centimeters and Weight in kilograms.

PROBLEM:

My problem is about storing unit of measure for the height and weight.

MY EFFORTS TO SOLVE THIS:

I could try to implement EAV model.

I could modify the table by adding extra columns that hold unit of measure for Height and Weight ? Something like below:

PersonTable < # ID int, 
                Name varchar(50), 
                Height double, $ HeightUnit_Id int, 
                Weight double, $ WeightUnit_Id int >

Units < #ID int, Desription varchar(20) >

HeightUnit_Id and WeightUnit_Id are foreign keys that reference Id from table Units.

QUESTION:

Is there a better solution than the obvious ones I mentioned above, since I dislike both ( EAV can get messy if done badly, and adding extra "unit columns" seems like a waste of space to me )?

Answer 1

Assuming that measurement types will not be mixed (i.e. any particular row will not mix "kg" and "inches" OR "pounds" and "cm"), and also assuming that at least part of the intention of this Question relates to this now deleted related Question (please note that the following link will not work unless you have enough rep points to see deleted items: Handling composite attributes with constant part ), then you need only to indicate the system of measurement being used by that row. In this model, you would have a single MeasurementSystem table that is Foreign Keyed to any tables containing measurements. For example (using Microsoft SQL Server syntax):

CREATE TABLE dbo.MeasurementSystem
(
  MeasurementSystemID TINYINT NOT NULL
                      CONSTRAINT [PK_MeasurementSystem] PRIMARY KEY
                                 CLUSTERED,
  MeasurementSystemName NVARCHAR(50) NOT NULL
);

dbo.Person
(
  PersonID            INT NOT NULL IDENTITY(1, 1)
                      CONSTRAINT [PK_Person] PRIMARY KEY
                                 CLUSTERED,
  MeasurementSystemID TINYINT NOT NULL
                      CONSTRAINT [FK_Person_MeasurementSystem]
                      FOREIGN KEY
                      REFERENCES dbo.MeasurementSystem (MeasurementSystemID),
  Name                NVARCHAR(50) NOT NULL
  Height              FLOAT,
  Weight              FLOAT,
  ...
);

INSERT INTO dbo.MeasurementSystem (MeasurementSystemID, MeasurementSystemName)
VALUES (1, N'Metric');

INSERT INTO dbo.MeasurementSystem (MeasurementSystemID, MeasurementSystemName)
VALUES (2, N'United States customary units');

If you will be mixing measurement systems and/or if you will be allowing for multiple units of measurement even if confined to one system (i.e. allowing for "mm", "cm", "m"), then there will need to be an additional layer to handle the increase in granularity, including the need to have one FK field per each measure column in the Person table. (I can adapt the model above to fit this but will wait for some clarification before doing so).

---

Or, if there will be a fairly finite / limited amount of combinations of various weight units and height units, then you can instead use each row to represent one of the accepted combinations (e.g. "cm & kg", "m & kg", "mm & kg" / "inch & lb", "foot & lb"). And then "US" vs "Metric" is just a property of each row of the lookup table. For example:

CREATE TABLE dbo.MeasurementUnit
(
  MeasurementUnitID     TINYINT NOT NULL
                                CONSTRAINT [PK_MeasurementUnit] PRIMARY KEY
                                           CLUSTERED,
  MeasurementSystem     CHAR(1) NOT NULL, -- "M" = Metric, "U" = US 
  MeasurementSystemName NVARCHAR(50) NOT NULL, -- "Metric" or "US Nonsense"
  HeightUnitsName       NVARCHAR(20) NOT NULL, -- "Centimeters"
  HeightUnitsAlias      NVARCHAR(5) NOT NULL, -- "cm"
  WeightUnitsName       NVARCHAR(20) NOT NULL, -- "Kilograms"
  WeightUnitsAlias      NVARCHAR(5) NOT NULL -- "kg"
);

dbo.Person
(
  PersonID            INT NOT NULL IDENTITY(1, 1)
                      CONSTRAINT [PK_Person] PRIMARY KEY
                                 CLUSTERED,
  MeasurementUnitID   TINYINT NOT NULL
                              CONSTRAINT [FK_Person_MeasurementUnit]
                                         FOREIGN KEY
                                        REFERENCES dbo.MeasurementUnit (MeasurementUnitID),
  Name                NVARCHAR(50) NOT NULL
  Height              FLOAT,
  Weight              FLOAT,
  ...
);

---

Or, if there needs to be combinations of Units that cross different measurement systems, then I would use separate tables -- one for each measurement type: "WeightUnits" and "HeightUnits". I wouldn't mix units for heights & weights in the same table (i.e. "kg" and "cm" on different rows). For example:

CREATE TABLE dbo.WeightUnit
(
  WeightUnitID      TINYINT NOT NULL
                            CONSTRAINT [PK_WeightUnit] PRIMARY KEY
                                       CLUSTERED,
  MeasurementSystem CHAR(1) NOT NULL, -- "M" = Metric, "U" = US 
  WeightUnitName    NVARCHAR(50) NOT NULL, -- "Kilograms"
  WeightUnitAlias   NVARCHAR(5) NOT NULL -- "kg"
);

CREATE TABLE dbo.HeightUnit
(
  HeightUnitID      TINYINT NOT NULL
                            CONSTRAINT [PK_HeightUnit] PRIMARY KEY
                                       CLUSTERED,
  MeasurementSystem CHAR(1) NOT NULL, -- "M" = Metric, "U" = US 
  HeightUnitName    NVARCHAR(50) NOT NULL, -- "Centimeters"
  HeightUnitAlias   NVARCHAR(5) NOT NULL -- "cm"
);

In this model, each measurement type in any given table has its own personal FK to its measurement units lookup table.

Answer 2

If your height/weight measure-units are indeed fixed, i.e. height=CMs, Weight=KGs, I would not introduce any additional columns (or logic) to specify the unit of measure. Instead, what I would do is just change the column name such as

create table Person (ID int, Name varchar(50), Height_CM double, Weight_KG double)

This way, I can remove any confusions about the measure unit for Height and Weight columns.

Answer 3

Seeing as how this question is in regard to design, the answers are likely to be fairly opinionated...

Here is my opinion on how it should be designed. ;)

Table to store units of measure

CREATE TABLE measure_unit (
  measure_unit_id, -- primary key
  name -- unique key .. values such as "inch" etc
)

Your person table ...

CREATE TABLE person (
  person_id, -- primary key
  name,
  measurement, -- example values would be 180
  measure_unit_id -- example values would be the pkey for centimeters
)

And then a conversion table

CREATE TABLE measure_conversion (
  from_measure_unit_id, -- example would be pkey for centimeters
  to_measure_unit_id, -- example would be pkey for inches
  ratio -- example would be 0.393701 (centimeters * 0.393701 = inches)
  -- pkey is composite between both from and to values
)

If a unit of measure doesn't have an entry in the measure conversion table for a specific other type of unit, then there would be no direct conversion available. (For example, cm -> inches would work, but cm -> hours wouldn't..) Displaying your measurements in different units should be easy.

Your measure unit can be anything you want (distance/time/etc) ... but you may want to add a measurement type .. not sure if I'd worry about that - depends on use case.

Answer 4

It depends on what you're designing... a normalized OLTP system would have a separate Measures (or some such name) table that provides referential integrity and performance optimizations for each possible unit of measure.

If you're doing analytics and you deal with units infrequently and do fewer conversions, it could be stored as an attribute. This would still be rare - something like survey responses or an industry standard like square footage for real estate. But what else gets measured and makes sense in real estate? floor plans? Now we have inches... and squares and cubics. We're back to a normalized design.

For height, a variety of measures are applicable and make perfect sense in various contexts. Probably a good idea to use a design something like this:

dbo.PersonHeight
Height     MeasureFK
62           2

dbo.UnitsOfMeasure
MeasureID  MeasureDescription
2          Inches
3          Centimeters

With such a design you can easily create a lookup/conversion table now:

dbo.UnitConversions
FromMeasureID    ToMeasureID    Conversion
2                 3             0.39370079