Re: Data Conversion

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(at)myrealbox(dot)com>
To: "Bob Pawley" <rjpawley(at)shaw(dot)ca>
Cc: "Postgresql" <pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly