Data normalization is a process of organizing the data in a database to reduce redundancy and improve integrity. It involves dividing the data into tables and establishing relationships among them based on some rules. In this article, you will learn why you should use data normalization in schema design and how it can benefit your information architecture.
Data normalization is a technique of designing a database schema that follows some principles called normal forms. Normal forms are criteria that define how well-structured a table is and how free it is from anomalies. Anomalies are problems that can occur when data is duplicated, inconsistent, or dependent on other data. By applying data normalization, you can avoid these issues and ensure that your data is accurate, consistent, and easy to maintain.
Data normalization offers several advantages for your information architecture, such as reducing data redundancy and storage space, improving data integrity and quality, simplifying data manipulation and querying, and enhancing data security and accessibility. By eliminating duplicate data, you can save space and avoid wasting resources. Furthermore, by enforcing constraints and relationships, you can guarantee that your data is valid and consistent across tables. Additionally, by organizing your data into smaller and simpler tables, you can perform operations and queries more quickly and accurately. Moreover, by separating your data into logical units, you can control who can access and modify your data and how.
One of the most important benefits of normalizing your data is that unintended consequences are ruled out by design. Consider the order table that has summative data from the order details, like total quantity ordered (for all items on the order). If the customer calls up and cancels an item, are you absolutely certain that you will get that data updated? And, are you literally handing off work to the development team? Kicking the can downstream and hoping it get's done correctly is not any sort of good practice.
Data normalization is not a perfect solution and it has some drawbacks that you should consider. For instance, it can increase the complexity and number of tables, which may require joining to retrieve the necessary information. Additionally, it can affect the performance and scalability of your database by creating more tables and relationships. Moreover, data normalization may require more skills and knowledge to design and implement, as it involves understanding the concepts and rules of normal forms.
While it's true that abstraction leads to a complex and less performant database structure, it's also true that it's easier to denormalize a well-structured schema than to do the opposite. Unless you are building short-lived applications or projects where speed and simplicity are more important than longevity, I would always prioritize normalization. This is even more important when your requirements are unstable or you expect them to change over time.
Data normalization is an iterative process that involves applying a series of normal forms to your tables, such as First normal form (1NF), Second normal form (2NF), and Third normal form (3NF). 1NF requires that each table has a primary key and that each column contains atomic values that cannot be further divided. 2NF demands that each table is in 1NF and that each column depends on the whole primary key, meaning no partial dependencies. 3NF requires that each table is in 2NF and that each column depends only on the primary key, with no transitive dependencies. Additionally, there are more advanced normal forms, such as Boyce-Codd normal form (BCNF), fourth normal form (4NF), and fifth normal form (5NF); however, these are less commonly used and beyond the scope of this article.
Data normalization should be balanced with data denormalization, a process of combining or replicating data to improve performance or usability. Denormalizing your data can be useful when you need to optimize queries for speed and simplicity, support analytical or reporting purposes, or accommodate specific business or user requirements. However, it can also increase data redundancy and storage space, reduce data integrity and quality, and complicate data manipulation and maintenance. Consequently, you should find the optimal balance between normalization and denormalization that meets your information architecture goals and trade-offs.
Having to plan or work towards denormalization means you ended up in a situation where your original architecture is not good enough or simply doesn't fit the current use case. That's the moment where you have to iron your source of trust, making sure it is as normalized as possible and going from there. Data redundancy or storage space are side effects and should not be considered issues as long as your source of trust is solid; you can always dispose of duplicates or spin up fine tuned instances optimized for specific use cases. That's actually more preferable than aiming for compromises on your main data source.