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
comparedto 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
intoun-normalize solution. Please advice if you realize I miss out several important points.
Thanks
Yan Cheng CHEOK