Should I Normalize My Database?

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 about the importance of normalizing databases in an almost religious fashion. You’re made to feel like failure to normalize a database is tantamount to violating one of the laws of the universe.

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

When Should You Normalize?

I’m not trying to turn computer science on its head. In fact, database normalization is a great idea and I encourage you to begin any database design endeavor with normalization in mind. If you can normalize your database, go for it! In fact, I offer some practical advice on how to normalize your database on this site:

• Putting Your Database in First Normal Form (1NF)
• Putting Your Database in Second Normal Form (2NF)
• Putting Your Database in Third Normal Form (2NF)

The bottom line is that you should normalize your databases unless you have a really good reason not to do so. Normalizing is usually sound design practice. It reduces redundant information, which 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 might seem like a simple query spanning five or ten 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 wasn’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!
2. 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’ve been banging your head against the table for an hour or two 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.
3. 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.

Some Words of Caution

Let’s not throw the baby out with the bath water here. As I mentioned up front, database normalization is generally a good idea. You should try to follow the principles of normalization when it seems reasonable to do so. My point here is that you shouldn’t let a quasi-religious fanaticism about normalization prevent you from doing your job in the best way possible.

As a parting thought, I offer some words of caution. When you do choose to vary from the rules of normalization, you’ll need to be extra vigilant about the way you enforce database integrity. If you store redundant information, put triggers and other controls in place to make sure that information stays consistent.