Re: Data Conversion - Mailing list pgsql-general

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