In what business database environments is de-normalization appropriate and why? What would de-normalization specifically involve?
I believe that in small businesses that do not require a system database with all of the bells and whistles or complex queries, de-normalization is more than appropriate. For example, I had worked for an organization that tracked Wetlands sites for maintenance projects. I was tasked with providing a simple system that would track contracts and maintenance cycles. I developed a very simple system to accomplish this task by having two tables with all of the data in the respective tables. The Wetlands Specialist could determine which sites needed to be maintained in a given year and which ones could be delayed or placed on a list of future mitigation projects. One table had most of the project information and the other was merely a look up table. Since normalization involves the reducing of excess data from tables and the creation of linking supplemental data tables together, I’ve always found that de-normalization is the process of putting as much information in one table that one could look at it and pull out the information they need quickly. However, that is what worked for this particular system but may not work in other cases.
Normalization is important but always remember that it is not the end all be all for database systems.
This is a great question. I always find it interesting to read the different responses from the entire class but I have always found the common theme or understanding of normalizing is to reduce redundancy within systems. Over the years I have tried to emphasize to customers the importance of Normalization because I look at redundancy as a reports/queries worst nightmare. The reason that I state this is the fact that these redundancies can skew results which in turn can produce negative outcomes for data users. If Normalization does not occur then the consequences can be catastrophic to any organization. Once organizations understand the importance of Normalization it produces the desired outcomes that will not only put a smile on the executive’s faces but can potentially save an organization from data failure.
What comes to mind when I think about this question is the old concept that “One size does not fit all”. Now this may be true in some instances but not all. Normalization is a perfect example. The levels of Normalization that occur with a system can be vastly different between the respective systems. This is why when requirements are being gathered and conceptual designs are being designed this is when Normalization is determined and at what level. So when determining at what level Normalization must occur you must clearly understand the data and requirements. Below is a great article about Normalization. Take a read and let me know your thoughts. 🙂
The disadvantage of normalizing a database to a high degree of normalization would include structural disadvantages such as breaking the database down into too many granular tables. When this occurs you may end up having to create more complex queries to the join the data that has been scattered across multiple tables. Another disadvantage would be reduced efficiency which is due to the data residing on multiple tables which will cause the system to work harder when a report is requested because of all data it needs to extract from the multiple locations. The parts for the system that it requires to work harder are the CPU’s and hard drives ultimately slowing the system down reducing its efficiency.
Getting rid of redundant data and inconsistent dependencies can reduce the size of your database system. However, doing this very act may incur a new cost for the organization when you restructure your tables within the database itself. The stakeholders must work with the Data Analyst to understand normalization and perform detailed analysis before even beginning to normalize the data if the system is a complex system. It is also possible to over-normalize a database and break it down into too many tables which will create its own set of problems. What could be some examples of this?