7

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 )?

    • Is all units comparable with each other? I.e can all attributes be measured in say meters? If that is the case one idea is to store everything in the same unit (for example meters). – Lennart Dec 7 '15 at 21:41
    • @Lennart: I do not know if I understood you correctly, but I think the answer to your question is yes. Every height is measured in meters. – AlwaysLearningNewStuff Dec 7 '15 at 22:13
    • 3
      I would change the field names to clarify what unit of measure they are used for. Height_cm, Weight_kg. Then use the application layer to accept or convert the user measurements. Are you concerned about tracking these values over time? – Jonathan Fite Mar 30 '16 at 13:42
    • @JonathanFite: Are you concerned about tracking these values over time? I do not understand the question. Let me try to clarify: at the moment, I do not see how would app that connects to a database figure out which units are used. Being inexperienced, and first time tackling this problem, I see 2 solutions: developers will read some kind of official documentation where used units will be stated, or I will store that info in the database. I chose the other solution as it seemed logical to me. Perhaps I am overreacting... – AlwaysLearningNewStuff Mar 30 '16 at 15:03
    • 2
      Well, people's weight and height change over time. If this is a medical database especially then historical information on what this person weighed at specific dates could be useful. As for the rest of your question, there is always going to be a document explaining the API and if you state in that document that "Height" is the height of the person expressed in cm (or meters or hands) then it is up to the application layer to display that and convert to local/other units if necessary. Don't resist the documentation route too strongly, it's common in the industry. – Jonathan Fite Mar 30 '16 at 15:12
    6
    +50

    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.

    • Indeed what you have right now is just what I need, there is only one potential pitfall I must try to avoid. Tables provided are only simple examples that illustrate the problem. In real model, I do not know if units client uses are officially standard. This is stupid example, but let us say that they might use some other unit for Height other than m/cm/mm ( or inch/foot/...). I wonder if I could alter dbo.MeasurementSystem with columns MeasuredParameter and UnitUsed just to be safe. Sample data could be something like 1 Height cm, 2 Weight kg and so on... Thank you for answering +1 – AlwaysLearningNewStuff Mar 30 '16 at 16:32
    • 1
      @AlwaysLearningNewStuff How many combinations might there be? Are clients allowed to enter measure types free-form? Please do not over-simplify the examples as it can mislead and waste a lot of time for both of us. But if the combinations are limited, you could have one entry per combination: "cm & kg", "m & kg", "mm & kg" / "inch & lb", "foot & lb". And then US vs Metric is just a property of each row of that lookup table. But I wouldn't mix units for heights & weights in the same table (i.e. "kg" and "cm" on different rows). Instead, I would do separate tables: HeightUnits / WeightUnits. – Solomon Rutzky Mar 30 '16 at 18:59
    • 1
      I have checked everything with the clients, and they like the idea you proposed. I will officially accept your answer and award the bounty. Thanks again. – AlwaysLearningNewStuff Mar 30 '16 at 19:32
    • @AlwaysLearningNewStuff Thanks and you're welcome :-). But, which idea was it exactly? I proposed one in the answer and then 2 variations in the comment above. If it was something mentioned in the comment then I would prefer to update the answer with that info. So please let me know. – Solomon Rutzky Mar 30 '16 at 19:37
    • 1
      It was the first one, from your answer. They use standard units, so there would be no problem. I would leave your comment since I believe it might be useful to future readers. On the side note, I think I went overboard with this, maybe units should be stated somewhere in the documentation. After researching a bit, I came to the conclusion that this approach might be the industry standard... – AlwaysLearningNewStuff Mar 30 '16 at 20:09
    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.

    1

    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.

    • There will be no conversions, every height will be measured in meters, and I can guarantee that in the future there will be no need for any conversion. I guess this leaves me only with measure_unit table, correct? – AlwaysLearningNewStuff Dec 7 '15 at 22:16
    • If you are only ever going to use one unit of measure, then the design you proposed in OP would WORK ... but I still would recommend this design (and yes, you would no longer have the measure_conversion table) – Joishi Bodio Dec 7 '15 at 22:19
    0

    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
    
    • There will be no conversions, every height will be measured in meters, and I can guarantee that in the future there will be no need for any conversion. – AlwaysLearningNewStuff Dec 7 '15 at 22:16

    Your Answer

    By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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