From: | Michael Glaesemann <grzm(at)myrealbox(dot)com> |
---|---|
To: | Bob Pawley <rjpawley(at)shaw(dot)ca> |
Cc: | Postgresql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Data Conversion |
Date: | 2006-02-01 01:18:38 |
Message-ID: | 72A88C70-BD09-4F61-B4F0-CADAB96B65E2@myrealbox.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bob Pawley | 2006-02-01 01:32:51 | Re: Data Conversion |
Previous Message | Dann Corbit | 2006-02-01 01:05:48 | Re: Data Conversion |