Normalizing vs. Denormalizing Databases GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > Apps
Should I Normalize My Database?
Normalization in the real world
By Mike Chapple Mike Chapple Writer University of Idaho Auburn University Notre Dame Former Lifewire writer Mike Chapple is an IT professional with more than 10 years' experience cybersecurity and extensive knowledge of SQL and database management. lifewire's editorial guidelines Updated on February 23, 2020 Tweet Share Email stevecoleimages / Getty Images Tweet Share Email Apps Best Apps Payment Services 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! 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: 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 it's 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! 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. 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 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. 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 how you enforce database integrity. If you store redundant information, put triggers and other controls in place to make sure that information stays consistent. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire The Basics of Database Normalization What Is Boyce-Codd Normal Form (BCNF)? Full Functional Dependency in Database Normalization How to Open, Edit, & Convert MDT Files An Introduction to Databases for Beginners Glossary of Common Database Terms How to Export Data to Excel Comparing the Top 5 Desktop Databases How to Create Users And Grant Permissions In MySQL Putting a Database in First Normal Form What Is the Definition of a Database Query? A Guide to Understanding Database Dependencies How to Reset a Samsung Tablet What Is a Database Schema? What Is mysqldump and How Do I Use It? Functional Dependency Definition Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies