Normalization protects against anomalies, but often comes at the cost of performance. For example, additional tables means writing more JOINs and even with indexes on the foreign key columns, this takes time in addition to the overhead of maintaining an index. Another example is the use of columns containing data calculated from other columns in the same table, say a coordinate conversion (a common task in astronomy); this dependence would be a breach of the 3NF rules but if there are a large number of rows it could be computationally expensive to calculate them each time they were needed. In such cases, it is not uncommon to include the extra columns and commit to taking care when making updates to the source columns from which the calculated values are derived.

It is possible to reduce the computational cost arising from normalization by simply not following all of the normalization rules, or by extensive use of NULLable columns; this does, of course, expose your database to an increased risk of anomalies as described earlier, so take care. In some cases, specific queries may be so important that the database is designed specifically to enable them to be performed quickly, perhaps with other tables featuring some of the same data set up to serve other important queries; this setup is often called a Data Warehouse and maintainers must put additional effort into preserving data consistency, or may regularly regenerate the warehouse from a normalized relational database which is itself being updated. The normalized database then serves as the consistent data store, with the warehouse acting as something like an optimised query vehicle.

A common denormalization is to include data—as with the coordinate conversions mentioned earlier—which have transitive dependencies, breaking the rules of 3NF; for example, our Instruments and Instruments_models tables are arranged so that an additional table JOIN is now necessary to get the information about measurement_type (and also measurement_unit). If that extra JOIN comes at too high a cost, the table setup from the 2NF version might be preferred for reasons of performance.

An understanding of normalization makes it easier to judge the risk from denormalization, in the trade-off for performance, and plan to mitigate those risks. It is generally good practice to normalize, study the performance of the resulting database, and then consider denormalizations to achieve performance goals.