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 <
jgodoy@gmail.com>
On Wed, Feb 3, 2010 at 23:20, Yan Cheng Cheok
<yccheok@yahoo.com> 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 (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general