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 00:20:57 |
Message-ID: | 0A94743E-5C6C-4026-8310-4B16CD85BC2A@myrealbox.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bob Pawley | 2006-02-01 00:53:21 | Re: Data Conversion |
Previous Message | Bob Pawley | 2006-02-01 00:02:15 | Data Conversion |