Recommended way to manage changes to preference.

[GaP42]

I am developing a small database application where various measurements can be recorded. I want to allow the measurements to be recorded in the units which suit the user/ measuring instrument: Length in centimetres or inches, mass in kg or pounds, energy in kJ or kcal, temp in C or F etc.
The table holding these preferences also holds a conversion factor; for Unit Of Measure 1 to Unit Of Measure 2.

tblConvert tblConvert

Measure	Unit1Name	Unit2Name	PrefSetting	ConversionFactorU1U2
Mass	kg	lb	Yes​	2.20​
Length	cm	in	Yes​	0.39​
Energy	kJ	kcal	Yes​	0.24​
BloodGlucose	mmol/l	mg/dl	Yes​	18.02​
BloodOxygen	kPa	mmHg	Yes​	7.50​
BloodPressure	kPa	mmHg	No​	7.50​
Temp	deg C	deg F	Yes​	1.80​

The user data entry form for recording measurements allows the values to be entered, and depending on the preference, displays the Unit1Name or Unit2Name as the caption for the specific entry (Temp [ deg C or deg F ], Blood Pressure [ kPa or mmHg ] etc).

The question is what is the recommended method for dealing with a change in the PrefSetting for any Measure?
Two approaches in consideration:
1. If the preference is changed, then apply the conversion factor as needed to update all values in tables that store the relevant observations/measurements. Generally it would not be expected that setting are changed, however it will happen.
2. Always store the values as Unit1 values, but simply display the entry in the preferred unit. So for Temp, say, on entry if Unit1 is preferred then no conversion. If Unit2 is preferred, then an entry of 100 (deg F) will be stored as (100-32) / 1.8 = 37.7778. This would also mean applying the same conversion on reports when needed, by checking preferences. Extracting raw data should be done with the Unit1 label.

I don't think it reasonable to store mixed values in records for any one measure.

Issues / thoughts / recommendations ?

 

[The_Doc_Man]

OK, one man's opinion, (1) determine if there is a widely preferred or legal standard (2) store everything in the "standard" units (3) display in other units based on user's preference. No way that I can tell you what is right. Only that you would do better in the concept of "uniform storage rules" because then someone could come behind you (say, if you were busy on another project) and look at what you had done. If you had some uniformity in that, they wouldn't curse your name to the heavens for such a messed up storage scheme.

 

[GaP42]

Widely preferred / legal can still be a "local decision" for a whole range of measurements - based political borders - eg Litres and Gallons.
However, accepting that the values are stored as per the design decision (say that was for Litres) then would mean if the user preference is Gallons, then the form needs to handle the conversion when presenting Gallons when retrieving a record, and on update/entry, to store the entered value in litres. The standard units used are indicated by reference to the table (as Unit1 in the scheme above), whereas that could not be relied upon for the alternate approach. It does keep that aspect simpler.
Documenting the design decision will be critical using either approach.
Thanks

 

[ebs17]

What would change in the view if you were dealing with 4 or 5 units instead of 2?

One approach would be to differentiate the application into input, storage + processing and output. The preferred unit would then be derived from the predominant processing, so that few conversions are necessary there and table data with index use can be used, especially when you have a lot of data.

 

[CJ_London]

Think you potentially have granularity within a unit- e.g. one user might enter 20 yards, another 60 feet. Or 15 inches, 1.25 feet, 3 metres or 3000 millimetres

 

[GaP42]

If there was a need to handle multiple possible Units of Measurement for a given Measurement Type the schema would need to be adjusted. I have not thought that through, however a junction table between the table holding measurements and measurement type would be required, holding the current option measurement Type option. However even with that schema, the option to take either approach in storage and conversion is still valid, if a little more involved.
Appreciate your view, and yes, the preferred unit (as UnitOM1 in the current layout) should be the one most likely to be employed (minimising conversion processing on entry and presentation).

Context matters - clearly the examples are to support UoM across different measurement systems - basically SI/Metric vs Imperial - and yes there are glitches between Imperial measurements in different places. It is not about the conversion of units within a measurement scheme (such as Metres to cm or inches to Yards). In terms of context a body weight is measured in kg (metric) or lbs (Non metric) not in tonnes or tons, or grams or ounces (new borns maybe).

So perhaps, if UoM1 is always fixed within the application (ie it is set up in one measurement scheme) and all measures are stored in these units then
1. the user may chose UOM2
2. may modify UoM2 and set the conversion factor to allow entry in those units and display the unit as the caption. That would mean a junction table would not be required. (notwithstanding other scenarios requiring more sophisticated support for UoM)
EDIT:
3. Supports the within measurement system conversions and preferences eg metres and cm etc

I am not expecting a large number of records in this application. However, yes, the capacity to process large volumes in the defined UOM1 and the conversion of those values if the first approach as described in the OP was adopted would/could lead to issues.

So considering the advice, it appears the second approach is preferred.

Thank you

 

[Josef P.]

Note:
I use a structure like this for unit conversions:
Table with units that have the respective conversion factor to their SI unit. However, this is only a definition, you could use any unit from the group.

Table tUnits:
- idUnit (int, PK)
- Unitcode (varchar)
- SiUnit (int, FK to tUnits.idUnit)
- SiConversionFactor (double)

Example:

idUnit	UnitCode	SiUnit	SiUnitConversionFactor
10​	m	10​	1​
11​	mm	10​	0,001​
12​	cm	10​	0,01​

Query as conversion table:

SELECT
     U1.UnitCode AS UnitFrom,
     U2.UnitCode AS UnitTo,
     [U1].[SiUnitCOnversionFactor]/[U2].[SiUnitCOnversionFactor] AS ConversionFactor
FROM
      tUnits AS U1
      INNER JOIN
      tUnits AS U2 ON U1.SiUnit = U2.SiUnit

Example data:

UnitFrom	UnitTo	ConversionFactor
m	m	1​
mm	m	0,001​
cm	m	0,01​
dm	m	0,1​
km	m	1000​
m	mm	1000​
mm	mm	1​
cm	mm	10​
dm	mm	100​
km	mm	1000000​

 

[CJ_London]

As an aside, many years ago I had an involvement in a project to create 'number equivalences' to help with number visualisation. i.e. 'if all the invoices generated this year were printed, the stack would be as high as the Eifel Tower', 'If all the solar panels in the world were laid in one place they would cover the Isle of Wight'. Others would cover distance, speed, weight etc.

The concept used a 'unit zero' and all measurements of whatever type had a conversion factor to convert to a number of unit zeroes

The project failed because of the difficulty at the time of getting a sufficient number of appropriate units for 'target' objects (Eifel Tower height, area of Isle of Wight, etc) for variety. Probably a lot easier now with the internet, but that did not exist at the time.