Thread: 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: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 > > 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 > > 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