I was reminded a few days ago of how important it is to have a solid understanding of normalization and why it is so important to implement. I would like to take some time to share that with everyone, especially developers.
Twice now, recently, I have been pinged by frustrated people asking me to help solve their SQL problems. I asked both what the exact problem was and how it was that they thought I could help. The root of both problems was bad normalization. That is, functional normalization had not been done. What follows is a case study of one of the situations including analysis of what went wrong as well as implementing basic normalization to address the current issue and avoid future issues.
One of the frustrated developers was put in a position to bolt on functionality that had never been planned for in the original application. This is certainly not a new phenomenon, but is evidence why developers need to understand functional normalization (maybe even more so than DBAs).
So what was the issue and how could it be avoided? The application needed to add language support for each one of its products. So now products would have a language associated with them, presumably this need arose from the company spreading into global markets. The developer decided to add a separate column for each supported language, in this case four. If the product was a "Spanish" product, the Spanish column would be set to 1 and the other language columns would be set to 0. Some of us see the problem inherent in this design already, but for those who don't, read on.
Before I continue, let us be clear, this proposed design change to support the new function will work! Function can be achieved on a broad spectrum of efficiency. The purpose of this blog is not to discuss the viability of inefficient design; it is to highlight what can be gained by efficient design.
First, let’s see how this design is in fact inefficient. Anytime a design choice guarantees a need for redesign in the future, it is by definition inefficient. Having a column for each language almost ensures that redesign will occur. What happens when support for a new language is adopted? Redesign, albeit simple, never-the-less, redesign. “Oh, no, we are only going to support four languages.”, I don’t buy it. I’m willing to bet something like this was said during the initial design, “Oh no, we will not need to support other languages.”. Function will change, plan for it.
There are two other main inefficiencies as well as their resolutions that I want to discuss. I will add those points in future additions to this blog post. Check back for the follow-ups and conclusion to this, basic but highly impactful discussion. Until then, stay thoughtful.
- Ed Dressler, SQL Server Expert; CTT+, MCDBA, and MCITP LinkIn with EdTwice now, recently, I have been pinged by frustrated people asking me to help solve their SQL problems. I asked both what the exact problem was and how it was that they thought I could help. The root of both problems was bad normalization. That is, functional normalization had not been done. What follows is a case study of one of the situations including analysis of what went wrong as well as implementing basic normalization to address the current issue and avoid future issues.
One of the frustrated developers was put in a position to bolt on functionality that had never been planned for in the original application. This is certainly not a new phenomenon, but is evidence why developers need to understand functional normalization (maybe even more so than DBAs).
So what was the issue and how could it be avoided? The application needed to add language support for each one of its products. So now products would have a language associated with them, presumably this need arose from the company spreading into global markets. The developer decided to add a separate column for each supported language, in this case four. If the product was a "Spanish" product, the Spanish column would be set to 1 and the other language columns would be set to 0. Some of us see the problem inherent in this design already, but for those who don't, read on.
Before I continue, let us be clear, this proposed design change to support the new function will work! Function can be achieved on a broad spectrum of efficiency. The purpose of this blog is not to discuss the viability of inefficient design; it is to highlight what can be gained by efficient design.
First, let’s see how this design is in fact inefficient. Anytime a design choice guarantees a need for redesign in the future, it is by definition inefficient. Having a column for each language almost ensures that redesign will occur. What happens when support for a new language is adopted? Redesign, albeit simple, never-the-less, redesign. “Oh, no, we are only going to support four languages.”, I don’t buy it. I’m willing to bet something like this was said during the initial design, “Oh no, we will not need to support other languages.”. Function will change, plan for it.
There are two other main inefficiencies as well as their resolutions that I want to discuss. I will add those points in future additions to this blog post. Check back for the follow-ups and conclusion to this, basic but highly impactful discussion. Until then, stay thoughtful.
No comments:
Post a Comment