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
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? |