36 Chambers – The Legendary Journeys: Execution to the max!

February 22, 2014

T-SQL Anti-Patterns: Lack Of Normalization

Filed under: Database Administration — Kevin Feasel @ 7:00 pm

Like the previous T-SQL anti-pattern, this is really all about taking advantage of what a relational database management system offers you.  The benefits of an RDBMS come from the relational algebra upon which they are based, and in order to get the best outcomes, you need to follow the rules of the game.  When it comes to relational database systems, normalization is the primary rule.

I’m not going to go into detail on normalization—that’s a talk of its own (and one which you can read on my corporate site; right now, that’s a sneak preview, as I haven’t given that talk anywhere yet).  Basically, there are a whole slew of anti-patterns around not normalizing your data.  They include (but are not limited to):

  1. Non-atomic attributes.  Think of a comma-separated list of phone numbers.
  2. Multi-valued attributes.  In this case, you might have a table with attributes ID, Name, PhoneNumber1, PhoneNumber2, and PhoneNumber3.
  3. Repeated columns.  This is very similar to the multi-valued attribute example above; imagine a table with ID, Name, ChemistryScore, EnglishScore, MathScore, ReadingScore, etc.

In each of these examples, you throw away some of the value of a relational database model.  By doing that, you make it easier to introduce bad data and can degrade performance—which is ironic, considering that the primary reason people tend not to normalize their data is to try to improve performance.

About these ads

1 Comment »

  1. […] Yesterday’s anti-pattern ended with me talking about performance.  The rest of my anti-patterns have to do primarily with performance.  The first entry in this category is the king of poorly-performing solutions:  the Entity-Attribute-Value model.  The single best presentation of the EAV model and its constituent failings is this one by Phil Factor.  What follows is my quick presentation of the problem. […]

    Pingback by T-SQL Anti-Patterns: EAV | 36 Chambers - The Legendary Journeys: Execution to the max! — February 23, 2014 @ 6:02 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 99 other followers

%d bloggers like this: