You can always normalize and not use an artificial key.
You'd end up with:
measurement (normalization)
===========
id | value | measurement_unit_id | measurement_type_id
------------------------------------------------------
> 1 0.23 mm width
> 2 0.38 mm width
> 2 0.72 mm width
>
>
> measurement_unit_id
> ===================
> value
> ----------
> mm
>
m
cm
in
cm²
m³
>
>
> measurement_type_id
> ===================
> value
> ----------
> width
>
area
depth
volume
And so on. You'd benefit from a normalized structure, you'd have
constraints checking for valid units and types and you wouldn't need join to
get the resulting information.
--
Jorge Godoy <[email protected]>
On Wed, Feb 3, 2010 at 23:20, Yan Cheng Cheok <[email protected]> wrote:
>
> For example, for the following table,
>
>
> measurement (without normalization)
> ===========
> id | value | measurement_unit | measurement_type
> ------------------------------------------------
> 1 0.23 mm width
> 2 0.38 mm width
> 2 0.72 mm width
>
>
> If I normalize to the following format, I will encounter several problem
> compared to table without normalization
>
>
>
> measurement (normalization)
> ===========
> id | value | measurement_unit_id | measurement_type_id
> ------------------------------------------------------
> 1 0.23 1 1
> 2 0.38 1 1
> 2 0.72 1 1
>
>
> measurement_unit_id
> ===================
> id | value
> ----------
> 1 | mm
>
>
> measurement_type_id
> ===================
> id | value
> ----------
> 1 | width
>
>
> (1) When rows grow to few millions in table measurement, the join operation
> on normalization table, is *much* slower compared to non-normalization
> table.
>
> One of the most frequent used query, To retrieve "value",
> "measurement_unit" and "measurement_type", I need to join measurement +
> measurement_unit_id + measurement_type_id.
>
> For non-normalization table, I need NOT to join.
>
> Right now, I cannot justify why I need to use normalization. I afraid I
> miss out several important points when I turn into un-normalize solution.
> Please advice if you realize I miss out several important points.
>
> Thanks
> Yan Cheng CHEOK
>
>
>
>
>
> --
> Sent via pgsql-general mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
