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




---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to