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

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

I would create functions. Store the data in one format, and convert
with functions as needed on the fly.

E.g.:
SELECT Fahrenheit_to_Celcius(Fahrenheit_temp) FROM temperatures;

If that seems kludgy for the users, then create views for them that
perform the needed conversions using the functions.

Whatever the most frequently used units are, store on those units. Then
the conversions are not needed as often. E.g.:

CREATE VIEW Celcius_Temperatures AS
SELECT Fahrenheit_to_Celcius(Fahrenheit_temp) AS Celcius_Temp FROM
temperatures;

________________________________________
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bob Pawley
Sent: Tuesday, January 31, 2006 4:02 PM
To: Postgresql
Subject: [GENERAL] Data Conversion

I'm looking for ideas on the direction I should take.

I'm constructing an engineering database. I want to include the
capability of converting engineering data to different systems. For
instance - Fahrenheit to Celsius, lbs/hr to gph to gpm and to liters per
minute.

My thinking is bouncing between
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.

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.

Any thoughts on how best to accomplish this task with Postgresql would
be appreciated. (I'm on Windows XP)

Bob Pawley

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

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

I'm not sure why it would be a stability issue. As for the
complexity, I think once it's implemented you wouldn't have to worry
about it by properly encapsulating that complexity, perhaps in
procedures. I guess one way to handle the dual conversion issue is to
produce a view (based on my previous example)

create view measurement_conversions_view as
select measurement_type
, measurement_unit_in
, measurement_unit_out
, factor
from measurement_conversions
union
select measurement_type
, measurement_unit_out as measurement_unit_in
, measurement_unit_in as measurement_unit_out
, 1::numeric / factor as factor
from measurement_conversions
union
select measurement_type
, measurement_unit as measurement_unit_in
, measurement_unit as measurement_unit_out
, 1 as factor
from measurement_units

It'd also be good to add a constraint (through a trigger) that
guarantees that if, for example, the length conversion m => in is the
measurement_conversions table, the conversion in => m can't be
inserted. This would prevent duplicates in the
measurement_conversions_view (and corresponding possible errors
arising from slightly different conversion results).

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???

It's not just a perception. You're duplicating the values. You need
to always make sure that you're inserting into, updating, and
deleting from all of the relevant tables. I think that would be a
maintenance nightmare.

Michael Glaesemann
grzm myrealbox com

I'm a little concerned about stability since my Postgresql application has
failed three times in the last couple of months. It seems to have failed
when too many things are happening at the same time - mostly things that
have been instigated by my pointing and clicking.

Bob

----- 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 5:18 PM
Subject: Re: [GENERAL] Data Conversion

Show quoted text

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

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

I'm not sure why it would be a stability issue. As for the complexity, I
think once it's implemented you wouldn't have to worry about it by
properly encapsulating that complexity, perhaps in procedures. I guess
one way to handle the dual conversion issue is to produce a view (based
on my previous example)

create view measurement_conversions_view as
select measurement_type
, measurement_unit_in
, measurement_unit_out
, factor
from measurement_conversions
union
select measurement_type
, measurement_unit_out as measurement_unit_in
, measurement_unit_in as measurement_unit_out
, 1::numeric / factor as factor
from measurement_conversions
union
select measurement_type
, measurement_unit as measurement_unit_in
, measurement_unit as measurement_unit_out
, 1 as factor
from measurement_units

It'd also be good to add a constraint (through a trigger) that guarantees
that if, for example, the length conversion m => in is the
measurement_conversions table, the conversion in => m can't be inserted.
This would prevent duplicates in the measurement_conversions_view (and
corresponding possible errors arising from slightly different conversion
results).

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???

It's not just a perception. You're duplicating the values. You need to
always make sure that you're inserting into, updating, and deleting from
all of the relevant tables. I think that would be a maintenance
nightmare.

Michael Glaesemann
grzm myrealbox com

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Feb 1, 2006, at 10:32 , Bob Pawley wrote:

I'm a little concerned about stability since my Postgresql
application has failed three times in the last couple of months. It
seems to have failed when too many things are happening at the same
time - mostly things that have been instigated by my pointing and
clicking.

It sounds like you definitely have something to track down in either
your application or your PostgreSQL installation. What do you mean by
failed? Is it the application or the PostgreSQL backend? While
performance can be affected by your DDL, it shouldn't influence
anything that could be described as a "failure". Also, what pointing
and clicking are you doing? Is this in your own app or a PostgreSQL
administration app such as phpPgAdmin or pgAdmin?

What version of PostgreSQL are you running? What platform? What
hardware? This information may help others on the list help you nail
down your "stability" issues.

Michael Glaesemann
grzm myrealbox com

I'm running version 8.1 on XP.

When I point and click on pgadmin tables too quickly, sometimes, the program
freezes and I get the Windows message about reporting the failure.

If I stay cool and 'deterministic' (in other words - slow) there doesn't
seem to be a problem.

However, this may not be a Postgresql problem as other applications have
'failed' at various times as well (Empire Earth being one).

I would look at Mac or others if I didn't need to develop in Windows.

Bob
----- 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 5:41 PM
Subject: Re: [GENERAL] Data Conversion

Show quoted text

On Feb 1, 2006, at 10:32 , Bob Pawley wrote:

I'm a little concerned about stability since my Postgresql application
has failed three times in the last couple of months. It seems to have
failed when too many things are happening at the same time - mostly
things that have been instigated by my pointing and clicking.

It sounds like you definitely have something to track down in either your
application or your PostgreSQL installation. What do you mean by failed?
Is it the application or the PostgreSQL backend? While performance can be
affected by your DDL, it shouldn't influence anything that could be
described as a "failure". Also, what pointing and clicking are you doing?
Is this in your own app or a PostgreSQL administration app such as
phpPgAdmin or pgAdmin?

What version of PostgreSQL are you running? What platform? What hardware?
This information may help others on the list help you nail down your
"stability" issues.

Michael Glaesemann
grzm myrealbox com

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster