{"id":2650,"date":"2008-07-21T10:36:00","date_gmt":"2008-07-21T10:36:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-myth-of-over-normalization\/"},"modified":"2026-09-21T12:28:04","modified_gmt":"2026-09-21T12:28:04","slug":"the-myth-of-over-normalization","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/opinion\/editorials\/the-myth-of-over-normalization\/","title":{"rendered":"The myth of over-normalization"},"content":{"rendered":"\n

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. <\/em><\/strong><\/p>\n\n\n\n

This editorial makes the case, with C.J. Date’s position and the comments it provoked.<\/em><\/strong><\/p>\n\n\n\n

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. <\/p>\n\n\n\n

The claim<\/h2>\n\n\n\n

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’).\u00a0<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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<\/i>. <\/p>\n\n\n\n

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. <\/p>\n\n\n\n

If you don’t, then it is time to tear up your database design and start again. <\/p>\n\n\n\n

Why denormalizing OLTP is the wrong fix<\/h2>\n\n\n\n

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. <\/p>\n\n\n\n

As the saying goes, “Normalize ’til it hurts, then denormalize ’til it works”. <\/em><\/p>\n\n\n\n

In fact, denormalization always leads eventually to tears. It complicates updates, deletes and inserts; it renders your database difficult to modify.\u00a0<\/p>\n\n\n\n

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’!<\/strong><\/p>\n\n\n\n

\n
\n
\n
\n

Simple Talk is brought to you by Redgate Software<\/h3>\n
\n Take control of your databases with the trusted Database DevOps solutions provider. Automate with confidence, scale securely, and unlock growth through AI. <\/div>\n <\/div>\n Discover how Redgate can help you<\/a>\n <\/div>\n <\/div>\n<\/section>\n\n\n
\n

FAQs<\/h2>\n\n

1. Is over-normalization a real problem or just a sign of bad design?<\/h3>\n
\n

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.<\/p>\n <\/div>\n

2. When is denormalization justified?<\/h3>\n
\n

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.<\/p>\n <\/div>\n

3. Do more joins make a query slower?<\/h3>\n
\n

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.<\/p>\n <\/div>\n <\/section>\n","protected":false},"excerpt":{"rendered":"

Is over-normalization real? An editorial argues it is mostly a myth: a well-normalized OLTP database rarely needs denormalizing; joins are not to blame.…<\/p>\n","protected":false},"author":200703,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":true,"footnotes":""},"categories":[143523,47125],"tags":[4168],"coauthors":[7955],"class_list":["post-2650","post","type-post","status-publish","format-standard","hentry","category-databases","category-editorials","tag-database"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2650","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/200703"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2650"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2650\/revisions"}],"predecessor-version":[{"id":113172,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2650\/revisions\/113172"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2650"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2650"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2650"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2650"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}