Re: Data Conversion - Mailing list pgsql-general

Two way conversion will be a neccesity. My thought was that dual conversion
could be not only complex but also have problems with stability.

Option 2 would be less complex and there would be less potential stability
problems. However, there is some perception of redundancy in having two or
more tables contain similar information. But, is it only a perception???

Perhaps there is another way???

Bob Pawley


----- Original Message -----
From: "Michael Glaesemann" <grzm@myrealbox.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Tuesday, January 31, 2006 4:20 PM
Subject: Re: [GENERAL] 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




---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend