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
