Re: Data Conversion - Mailing list pgsql-general

From Michael Glaesemann
Subject Re: Data Conversion
Date
Msg-id 0A94743E-5C6C-4026-8310-4B16CD85BC2A@myrealbox.com
Whole thread Raw
In response to Data Conversion  (Bob Pawley <rjpawley@shaw.ca>)
List pgsql-general
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




pgsql-general by date:

Previous
From: Bob Pawley
Date:
Subject: Data Conversion
Next
From: Bob Pawley
Date:
Subject: Re: Data Conversion