Shall I apply normalization in the following case?

From: Yan Cheng Cheok <yccheok(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Shall I apply normalization in the following case?
Date: 2010-02-04 01:20:51
Message-ID: 433328.5821.qm@web65716.mail.ac4.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sean Hsien 2010-02-04 01:21:46 serial columns with replication/cluster
Previous Message Joe Conway 2010-02-04 01:08:07 Re: Is it necessary to have index for child table in following case?