Over-normalization is mostly a myth. A database normalized to third normal form or beyond is not slower for being well-designed; the performance problems blamed on “too many joins” almost always come from missing indexes, poor query design or a schema that was never properly normalized in the first place – and denormalizing an OLTP database to fix them trades integrity for a speed gain the optimizer would have given you anyway.
This editorial makes the case, with C.J. Date’s position and the comments it provoked.
I’ve always been suspicious of denormalizing an OLTP database. Denormalization is a strange activity that is supposed to take place after a database has been normalized, and is assumed to be necessary in order to reduce the number of joins in queries to a tolerable level.
The claim
C.J. Date is quite clear on this; well, he is slightly less opaque than usual: any denormalization to a level below 5NF is a ‘bad thing’ (he says ‘contraindicated’).
In practice, normalization to fifth Normal Form is unusual. Normally, the Database designer reaches for his hat and coat after reaching Boyce/Codd Normal Form (BCNF), which is 4NF, and few databases I’ve ever seen are even reliably BCNF.
Why does one ever normalize a database? It is often said that it is to avoid logical inconsistencies, and to avoid insertion, delete and update anomalies. Also, to avoid redundancy, or duplication, of data. I think there is more to it than that. It also ensures that your data model makes logical sense.
If, at the end of the normalization process, you arrive at a set of tables that correspond to simple, easily understood entities, then the chances are that you have got a database model that will sail through the inevitable changes in scope, changes in the application, extensions and so on.
If you don’t, then it is time to tear up your database design and start again.
Why denormalizing OLTP is the wrong fix
Normalization isn’t like sprinkling on fairy-dust; it is a way of testing and ‘proving’ your design. Too often, denormalization is suggested as the first thing to consider when tackling query performance problems. It is said to be a necessary compromise to be made when a rigorous logical design hits an inadequate database system.
As the saying goes, “Normalize ’til it hurts, then denormalize ’til it works”.
In fact, denormalization always leads eventually to tears. It complicates updates, deletes and inserts; it renders your database difficult to modify.
Maybe once there was an excuse for a spot of denormalization, but on a recent version of SQL Server or Oracle, with indexed or materialized views, and covering indexes, the performance hit from multiple joins in a query is negligible. If your database is slow, it isn’t because it is ‘over-normalized’!
Simple Talk is brought to you by Redgate Software
FAQs
1. Is over-normalization a real problem or just a sign of bad design?
Usually a sign of bad design – or of blaming the schema for something else. Genuine over-normalization (splitting attributes that are always used together into separate tables for no integrity reason) is rare; what people call over-normalization is more often a normalized schema queried without indexes, or one that was normalized wrongly. Fix the query and the indexes before you denormalize.
2. When is denormalization justified?
For read-heavy reporting or analytical copies of the data, where redundancy is deliberate and refreshed from the normalized source; for measured hot paths where a materialised or indexed view is not enough; and for storing computed aggregates that would otherwise be recalculated constantly. In an OLTP database, denormalizing the source of truth should be the last resort, after indexing and query fixes.
3. Do more joins make a query slower?
Not in themselves. The optimizer joins indexed tables efficiently, and a normalized schema often reads fewer pages than a wide denormalized one. Joins become expensive when the join columns are not indexed or the query returns far more rows than needed – which are query problems, not normalization problems.
This document contains proprietary information and is protected by copyright law.
Copyright © 2026 Red Gate Software Limited. All rights reserved
Load comments