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