I've been brought up old school - where we learned to design the database schema BEFORE the application's business layer (or using OOAD for everything else). I've been pretty good with designing schemas (IMHO :) and normalized only to remove unnecessary redundancy but not where it impacted speed i.e. if joins were a performance hit, the redundancy was left in place. But mostly it wasn't.

With the advent of some ORM frameworks like Ruby's ActiveRecord or ActiveJDBC (and a few others I can't remember, but I'm sure there are plenty) it seems they prefer having a surrogate key for every table even if some have primary keys like 'email' - breaking 2NF outright. Okay, I understand not too much, but it gets on my nerves (almost) when some of these ORMs (or programmers) don't acknowledge 1-1 or 1-0|1 (i.e. 1 to 0 or 1). They stipulate that it's just better to have everything as one big table no matter if it has a ton of nulls "todays systems can handle it" is the comment I've heard more often.

I agree that memory constraints did bear a direct correlation to normalization (there are other benefits too :) but in today's time with cheap memory and quad-core machines is the concept of DB normalization just left to the texts? As DBAs do you still practice normalization to 3NF (if not BCNF :)? Does it matter? Is "dirty schema" design good for production systems? Just how should one make the case "for" normalization if it's still relevant.

(Note: I'm not talking about datawarehouse's star/snowflake schemas which have redundancy as a part/need of the design but commercial systems with a backend database like StackExchange for example)

closed as primarily opinion-based by Max Vernon, Mat, dezso, Kin Shah, Jon Seigel Sep 20 '13 at 17:29

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.


One reason for normalisation is to remove data modification anomalies
ORMs usually do not support this.

I have many examples of Hibernate-designed databases that break this principle:

  • bloated (string repeated over 100s of millions of rows)
  • no lookup tables (see above)
  • no DRI (constraints, keys)
  • varchar clustered indexes
  • unnecessary link tables (eg enforcing 1..0:1 when a nullable FK column would suffice)

The worst I've seen is a 1TB MySQL database that was perhaps 75-80% too big because of these

I'd also suggest that the statement "todays systems can handle it" is true for most Mickey Mouse systems. As you scale, today's systems won't.

In my example above, there was no traction to refactor or change keys or fix data: just complain about database growth rates and inability to build a meaningful DW on top of it


it seems they prefer having a surrogate key for every table even if some have primary keys like 'email' - breaking 2NF outright.

Surrogate keys don't break 2NF. 2NF says "If a column is dependant on only part of a multi-valued key, remove that column to a separate table."

They stipulate that it's just better to have everything as one big table no matter if it has a ton of nulls

Having several columns in one table is valid as long as Normalization rules are followed. It is not correct to merge tables without analysis if you want to reap the benefits of SQL and normalization.

I agree that memory constraints did bear a direct correlation to normalization Relation Normal Forms is a mathematical concept and has nothing to do with memory.

Normalization is there not only to save memory or disk, it is there to add integrity. After all it is a mathematical concept independent of hardware.

Simple Example: Say you maintain school information as:

Rec 1:North Ridge High School, California, USA

Rec 2:South Toronto Braves High School, Ontario, Canada

If you ask your system where is Ontario, you can find out that it is in Canada. Few days later you delete the 2nd row and ask the system the same question and you get nothing. In this example, no mater how much disk space or memory or CPU, you will not get the answer.

This is one anomaly normalizing relations help prevent.

Edit: Changed the word Toronto to Ontario as per comment below.


The more things change, the more they stay the same. There have always been lazy developers who cut corners or just don't know or want to follow best practices. A lot of the time they can get away with it in smaller applications.

It used to be jamming COBOL-inspired data structures into early RDBMS, or the God-awful mess that was dBase. Now it's ORMs and "Code-First". In the end, these are all just ways of people trying to find the silver bullet of getting a working system without "wasting" time thinking hard about what you want and need to do. Being in a hurry has always been a problem and will always be a problem.

For those who have the good sense (and good luck) to take the time to design properly, the data model will always be the most logical place to start. What goes in the database is information about the things (tangible and intangible) about which your business cares. What your business cares about changes much less quickly than how your business operates. This is why your database is generally much more stable than your code.

The database is the rightful foundation of any system and taking the time to lay your foundations properly will inevitably benefit you over the long run. That means that normalization will always be an important, useful step for any OLTP type application.


I agree that memory constraints did bear a direct correlation to normalization...

Memory constraints still matter. Quantity isn't a problem, speed is.

  • CPUs aren't getting any faster at the moment (We're getting more core's, not cycles per second)
  • Modern CPU architectures attempt to overcome the speed limitation by providing separate memory for each processor (NUMA).
  • On-die cache sizes aren't growing at a comparable rate to main memory.
  • Memory throughput is not as high as most people expect. QPI is in the region of 25GB/sec.

Some of this ground was covered in When to use TINYINT over INT? which you might find useful. I'd also suggest following the antics of @ThomasKejser (blog) from the SQLCAT team, as they tend to be at the sharp edge of pushing database performance. The recent post on The Effect of CPU Caches and Memory Access Patterns and the SQLBits presentation on Relational Modeling for Extreme DW Scale are good examples.


In my opinion, it is still just about balance between normalize & de-normalize. I totally agree that ORM frameworks are merely approaches getting things done, but I don't think it is these frameworks causing the de-normalize trend.

it is still that debate you want time efficiency or you want space efficiency. At the time Relational Database theory is brought up, the disk storage is expensive, people obviously don't want to spend that much money on this, that is why at that time relational databases are the one who stands firm amid adversities

Now days things are quite different, storage are very very cheap. So obviously we can tolerate more redundancy compared with old days, this is also WHY the BIG_TABLE approach appeared. in order to seeking more time efficiency , the space efficiency has to be sacrificed.

But, Big-table approach is not the end of story either, it is still the balance between time and space, in terms of PB volume data to manage, some developer also began to seek the balance back to space efficiency, that is why there are works done to normalize some data in BIG-TABLE like structures.

In a word, the normalization approach is not dead definitely, but compared with the old days it is definitely overlooked.


C.J Date answers your question here - the normalization (prelim) video is free.


The short answer: normalization is the mathematically correct way of doing things. If you don't normalize properly, your data model is simply incorrect.

Not the answer you're looking for? Browse other questions tagged or ask your own question.