standard solution for storing units of measure INTRODUCTION AND RELEVANT INFORMATION:

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

PersonTable &lt; # ID int, Name varchar(50), Height double, Weight double &gt;


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 &lt; # ID int, 
                Name varchar(50), 
                Height double, $ HeightUnit_Id int, 
                Weight double, $ WeightUnit_Id int &gt;

Units &lt; #ID int, Desription varchar(20) &gt;




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 )?
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 &amp; kg", "m &amp; kg", "mm &amp; kg" / "inch &amp; lb", "foot &amp; 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 &amp; 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.