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 

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 

Units 

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 & 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.

StackExchange Database Administrators Q#123137, answer score: 7