Architectural Principles Units of Measure

From PPDM Wiki
Jump to: navigation, search

Numeric, measured quantities in the database must be qualified with a “units of measure” indicator. At times, the original units of measure must also be retained for regulatory, audit or tracking purposes.

Contents

Objectives

The Units of Measure module in PPDM allows the member to:

  1. Provide and identify the UOM for all numeric value in the database.
  2. Capture the original UOM where necessary.
  3. Identify and adopt a standard set of conversions. A consistent method of conversions is necessary to change values between different systems of units.
  4. Provide standard set of conversion values and algorithms for consistent conversion that does not introduce value creep as conversions are repeatedly applied.
  5. Provide a standard set of mnemonics.

Guidelines

  1. Provide UOM for numeric values at the Meta table level (PPDM_COLUMN. DEFAULT_UOM_ID).
  2. Where the UOM for a column depends on the data (such as a production volume) provide UOM on a table column basis.
  3. Provide the Original UOM (OUOM) in the host table where business needs dictate. Regulatory or legal requirements dictate that the original value and original UOM be available for certain numeric values.
  4. The name of the OUOM column (and the UOM column, where used) should contain the same prefix as the column containing the value. (i.e. MEASURED_DEPTH and MEASURED_DEPTH_OUOM)

Method

There is a reference guide fully describing how Units of Measure are handled in PPDM.

  1. All table names are stored in PPDM_TABLE.
  2. Column names are stored in PPDM_COLUMN.
  3. Recursive foreign key relationships within PPDM_COLUMN associate measurement columns with the appropriate UOM or OUOM columns.
  4. PPDM_COLUMN_GROUP: This serves as the mechanism to group together columns logically. This can be done as a "domain" or as a totally different type of grouping. We may need a lookup table.
  5. PPDM_MEASUREMENT_SYSTEM: Defines valid systems of measure. For example, Metric, US Imperial, British Imperial and so on.
  6. PPDM_UNIT_OF_MEASURE: Defines the valid units of measure within a measurement system.
  7. PPDM_DATA_STORE: This is intended to allow the grouping of tables into areas that may use different units of measure as defaults. It has been suggested that it may work well for project databases. This is still open for discussion.
  8. PPDM_UOM_ALLIAS: Intended to be an aid during data loads. Other names for units of measure.
  9. PPDM_QUANTITY: Defines the quantity - length, volume, area and so on.
  10. PPDM_UNIT_CONVERSION: Defines valid conversions between units of measure.


Currency Units of Measure

Currency values in PPDM should all be stored as a single currency (i.e. all US$ or CDN$) to avoid performance degradation through on-line conversions and calculations or full table scans. However, there may be a need to restore the original value of the currency in the units originally received as. To accommodate this, currency values should be modeled as:

  • XXX (cost value) number (12,2)
  • CURRENCY_OUOM varchar2 (12)
  • CURRENCY_CONVERSION number (10,5)

Currency conversion rates are not static, and consequently cannot be managed as other conversion factors (in the UOM Module). Every row of data must be accompanied by the conversion factor relevant at the time of the transaction. The currency conversion factor is the value which, when multiplied by the cost value, results in the original value of the cost in the Original units of currency.

Usually, it should only be necessary to store one conversion value per table in PPDM. However, if necessary, additional columns for currency conversion may be specified and named to indicate the values to which they refer.

Back to Architectural Principles Index

Retrieved from "http://www.ppdm.org/wiki/index.php/Architectural_Principles_Units_of_Measure"
Personal tools