Re: Data Conversion

On Feb 1, 2006, at 9:02 , Bob Pawley wrote:

>             1 – creating a single table of data in the format of
> the users’ choice, then converting the data en masse as the user
> requests. Sort of like conversion-on-demand.

I've been thinking about a similar application recently, and leaning
towards your first option. A rough sketch of the schema that I've
been considering looks like this:

create table measurement_types
(
    measurement_type text primary key
);

copy measurement_types (measurement_type) from stdin;
length
mass
temperature
\.

create table measurement_units
(
    measurement_unit text primary key
    , measurement_type text not null
        references measurement_types (measurement_type)
);

copy measurement_units (measurement_type, measurement_unit) from stdin;
length    in
length    m
length    miles
temperature    F
temperature    C
mass    kg
\.

create table measurement_conversions
(
    measurement_type text not null
    , measurement_unit_from    text not null
    , measurement_unit_to    text not null
    , conversion_factor    numeric not null
    , unique (measurement_unit_from, measurement_unit_to)
    , foreign key (measurement_type, measurement_unit_from)
        references measurement_units (measurement_type, measurement_unit)
    , foreign key (measurement_type, measurement_unit_to)
        references measurement_units (measurement_type, measurement_unit)
);

-- Haven't thought through yet how to handle conversions in the other
direction. I'd rather not include, for example, F => C and C => F.
Also, do I need to include F => F?

create table data_records
(
    measurement_id serial primary key
    , measurement_unit text not null
        references measurement_units (measurement_unit)
    , measurement_value numeric not null

);

>             2 – creating tables for each format (for temperature
> that would be one Fahrenheit table and one Celsius table) and do
> the conversion as the data is entered.
This smacks of duplication of data, which relational databases are
meant to avoid.

Anyway, hope this helps.

Michael Glaesemann
grzm myrealbox com