> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
