An Analysis of Normalization Through the Eyes of the Developer - Part III

View Part II

Paraphrasing E.F. Codd’s First Normal Form, it states that there should be, “No repeating groups”.  The language column is the group in this example, below you will find the proposed design implements this rule, while the original design does not.

Original design

(Products table)

ProductID	Name	English	Spanish	French	Italian
1	Ceramic tile	1	0	0	0

Proposed design

(Products table)

ProductID	Name	LanguageID
1	Ceramic tile	1

(Language table)

LanguageID	Name
1	English
2	Spanish
3	French
4	Italian

Consider the original design and the proposed design above.  The proposed design removes all of the hindrances that the original design put into place.  Normalization frees the developer from handling issues, because those issues are simply not present.

Problem 1 solved:  Suppose the business expands into a German speaking market, in the original design a column must be added and populated with a “0” for all current and subsequent records.  In the proposed design, simply adding a fifth record to the language table is the only task that needs to be done.

Problem 2 solved:  Keeping data clean is much simpler in the proposed design.  If a product needs a different language, simply change the LanguageID field in the Product table.  In the original design, this would, as best, involve altering data in two different columns.

Problem 3 solved:  In the proposed design, there is no need to store data that is essentially keeps track of the language that the product is not.  In the proposed design, if a product is not associated with a language, a record simply does not exist.

I hope this blog has shown the benefits of simple normalization.  Making the decision to architect even your simplest of applications with normalization can guard against unforeseen complications, while keeping the focus on the business need.

- Ed Dressler, SQL Server Expert; CTT+, MCDBA, and MCITP LinkIn with Ed

An Analysis of Normalization Through the Eyes of the Developer - Part I

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.

View Part II

- Ed Dressler, SQL Server Expert; CTT+, MCDBA, and MCITP LinkIn with Ed

Ask Your Instructor: SQL Server FAQ's Part II

Got SQL Server questions? I love them! Even when I don’t have the answer right then!

I recently delivered a class on “Maintaining a Microsoft SQL Server 2008 Database” and I promised my students that I would capture their ad-hoc questions (and even some answers) in a document and, thinking that it was possible that someone else out in the web ether might have had the same question, decided to share them.

So here they are in all their glory. Enjoy!

8) Can you have multiple replication types going INTO one database?
Yes, but you must manage objname collisions manually.

9) Can you replicate from a mirror? 
Sort of… http://msdn.microsoft.com/en-us/library/ms151799.aspx  My understanding of the question is can you replicate from the database that is the CURRENT mirror. So far all of my tests have proven to me that I cannot, (but I am sure someone out there is up to the challenge). Never say it can’t be done unless you can prove it!

10) Does the restore of a database require re-initializing of a publication? 
Yes, if you want to move all copies of the data BACK to the restore point. Otherwise use synchronization to bring the restored database up to date with the subscribers.

11) Will changing the root of my IIS installation break Reporting Services?
No, IIS is handling the connection to the root of the web service so Reporting Services will simply be redirected to the new location.

12) Can you use SQL Server Express Edition as a WITNESS in a Mirror? 
Yes, you can use Express, Workgroup, Standard or Enterprise (SQL Server 2005 and later) as a WITNESS, although the Mirror and Principal can only be created on Standard and Enterprise. http://msdn.microsoft.com/en-us/library/ms175191.aspx

13) What is the heartbeat setting of a Mirror and can I change it?
The default time is 10 seconds but it can be modified using this statement on the Principal: ALTER DATABASE SET PARTNER TIMEOUT 

14) Can I run the SQL Server 2008 Profiler on SQL Server 2000? 
Yes

The questions I get from students keep these courses interesting for me and I would love to hear your questions through this blog or in my class. After all, as a long time veteran Microsoft employee told me a few years ago, “After 10 years of work on Microsoft products with a dozen certifications, you can only attain about 1% knowledge of the Microsoft technologies.”

Ok, I gotta go hit the books. My next class is probably going to ask about the other 99%... again. Stay tuned for Part II of this post!


- Peter Trast, SQL Expert; MCITP DBA, MCITP EA, MCT LinkIn with Peter

Ask Your Instructor: SQL Server FAQ's Part I

Got SQL Server questions? I love them! Even when I don’t have the answer right then!

I recently delivered a class on “Maintaining a Microsoft SQL Server 2008 Database” and I promised my students that I would capture their ad-hoc questions (and even some answers) in a document and, thinking that it was possible that someone else out in the web ether might have had the same question, decided to share them.

So here they are in all their glory. Enjoy!

1) Is there any reason to use Binary Collation?
It is the fastest. More on collations at http://msdn.microsoft.com/en-us/library/ms143515(v=SQL.90).aspx

2) Can you backup and restore an entire instance?
Not in the same way as a database, no, BUT, you can right-click on the instance, choose Facets, and “Export Current State as Policy” and use this exported data with Policy Based Management to apply against another instance or even multiple instances.

3) Is sqlcmd -A (dedicated administrator connection) for the local host only? No, you can connect to a remote server using: sqlcmd –A –S ServerName

4) Where can I get a list of torn pages?
(USE msdb) SELECT * FROM suspect_pages

5) Are Windows credentials cached in a SQL Server session?
Yes, until the connection is ended

6) Where can I get a list of certificates in SQL Server?
SELECT * FROM sys.certificates

7) Is there an issue with resolving conflicting Identity columns in HTTP merge replication?
Identity columns cannot be ADDED to a publication (GUID’s are used for uniqueness) but they can be used if different values are used in the publication and each subscriber http://msdn.microsoft.com/en-us/library/ms152543.aspx

The questions I get from students keep these courses interesting for me and I would love to hear your questions through this blog or in my class. After all, as a long time veteran Microsoft employee told me a few years ago, “After 10 years of work on Microsoft products with a dozen certifications, you can only attain about 1% knowledge of the Microsoft technologies.”

Ok, I gotta go hit the books. My next class is probably going to ask about the other 99%... again. Stay tuned for Part II of this post!


- Peter Trast, SQL Expert; MCITP DBA, MCITP EA, MCT LinkIn with Peter