On Feb 1, 2006, at 9:02 , Bob Pawley wrote:
1 – creating a single table of data in the format of
the users’ choice, then converting the data en masse as the user
requests. Sort of like conversion-on-demand.
I've been thinking about a similar application recently, and leaning
towards your first option. A rough sketch of the schema that I've
been considering looks like this:
create table measurement_types
(
measurement_type text primary key
);
copy measurement_types (measurement_type) from stdin;
length
mass
temperature
\.
create table measurement_units
(
measurement_unit text primary key
, measurement_type text not null
references measurement_types (measurement_type)
);
copy measurement_units (measurement_type, measurement_unit) from stdin;
length in
length m
length miles
temperature F
temperature C
mass kg
\.
create table measurement_conversions
(
measurement_type text not null
, measurement_unit_from text not null
, measurement_unit_to text not null
, conversion_factor numeric not null
, unique (measurement_unit_from, measurement_unit_to)
, foreign key (measurement_type, measurement_unit_from)
references measurement_units (measurement_type, measurement_unit)
, foreign key (measurement_type, measurement_unit_to)
references measurement_units (measurement_type, measurement_unit)
);
-- Haven't thought through yet how to handle conversions in the other
direction. I'd rather not include, for example, F => C and C => F.
Also, do I need to include F => F?
create table data_records
(
measurement_id serial primary key
, measurement_unit text not null
references measurement_units (measurement_unit)
, measurement_value numeric not null
);
2 – creating tables for each format (for temperature
that would be one Fahrenheit table and one Celsius table) and do
the conversion as the data is entered.
This smacks of duplication of data, which relational databases are
meant to avoid.
Anyway, hope this helps.
Michael Glaesemann
grzm myrealbox com
