> 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) foreign key constraints are important, so you don't have things
misspelled or spelled differently and to define the "official" value.
2) querying on an int is quicker then querying on a string, so if you
query on the values without the join you will have better performance.
3) You might want to have more information in the other tables one day,
such as unit conversion information or descriptions, etc..
4) depending on the size of the string, it might take less space for an
int. Though a varchar with mm only takes 3 bytes, width takes 6 bytes,
while a regular int takes 4.
5) As Jorge mentioned you can make the value your pk instead of a serial
int and then you have it normalized and readable.
For the specific design that you are showing, there is no real benefit
to normalization, other then it would make it more scalable.
Sim
