Обсуждение: 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


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