Should I Normalize My Database?

Normalization in the Real World

Database normalization is one of the sacred cows of application development. Every undergraduate programming course you’ve taken or book you’ve read likely preaches the importance of normalizing databases.

It’s time to challenge that truism. Sometimes it’s OK to denormalize your database!

When Should You Normalize?

Database normalization protects your data's integrity. It's a great idea in many cases, and you should begin any database design endeavor with normalization in mind.

If you can normalize your database, go for it! In fact, Here is some practical advice on how to normalize your database on this site:

The bottom line is that you should normalize your database unless you have a really good reason not to do so. Normalizing is usually sound design practice. It reduces redundant information, optimizes performance and reduces the likelihood that you’ll have data integrity issues that result from having the same data stashed in different corners of your database.

Some Good Reasons Not To Normalize

That said, there are some good reasons not to normalize your database. Let’s look at a few:

  1. Joins are expensive. Normalizing your database often involves creating lots of tables. In fact, you can easily wind up with what you think should be a simple query that spans five or 10 tables. If you’ve ever tried doing a five-table join, you know that it works in principle, but its painstakingly slow in practice. If you’re building a web application that relies upon multiple-join queries against large tables, you might find yourself thinking, “If only this database weren’t normalized!” When you hear that thought in your head, it’s a good time to consider denormalizing. If you can stick all of the data used by that query into a single table without really jeopardizing your data integrity, go for it! Be a rebel and denormalize your database. You won’t look back!
  1. Normalized design is difficult. If you’re working with a complex database schema, you’ll probably find yourself banging your head against the table over the complexity of normalization. As a simple rule of thumb, if you're spending all day trying to figure out how to move to the fourth normal form, you might be taking normalization too far. Step back and ask yourself if it’s really worth continuing.
  1. Quick and dirty should be quick and dirty. If you’re just developing a prototype, just do whatever works quickly. Really. It’s OK. Rapid application development is sometimes more important than elegant design. Just remember to go back and take a careful look at your design once you’re ready to move beyond the prototyping phase. The price you pay for a quick and dirty database design is that you might need to throw it away and start over when it’s time to build for production.
  2. If you're using a NoSQL database, traditional normalization is not desirable. Instead, design your database using the BASE model which is far more forgiving. This is useful when you are storing unstructured data such as emails, images or videos.

Some Words of Caution

Database normalization is generally a good idea. You should try to follow the principles of normalization when it seems reasonable to do so. But if all indicators point to normalization being too complex to implement, consider an approach that will get the job done while still protecting your data. 

Finally —  if you do choose to stray from the rules of normalization, be extra vigilant about the how you enforce database integrity. If you store redundant information, put triggers and other controls in place to make sure that information stays consistent.