Saturday, March 15, 2008

Normalization for Normal People--Introduction

As an administrator and regular contributor at, I see, and sometimes answer, a lot of questions asking for help in designing forms, or creating queries or writing functions to enter and display, retrieve or update data. Eight times out of ten, the real, underlying, problem turns out to be inappropriate table design. Poor table design often makes it difficult—if not impossible—to enter or retrieve data in any meaningful way. The best advice for the people who present those questions is usually, “Forget what you’re trying to do now. Go back, normalize your tables, and start over.” Unfortunately, many of them refuse to do that and plunge ahead building ever more complex and unwieldy structures to compensate.

I could tell you horror stories about my own initial failures in table design as well. I'm especially fond, if that's the right word, of the first db I ever built. I tied the company address to the first employee record entered. When that employee left to take a new job, she took the address of her former employer with her. And then, when the user "corrected" that address, we lost any record of what that first employer's address had been. Try explaining THAT without hearing the word "idiot" somewhere in the conversation.

I've come a long way since then, but I am passionate about normalization because I’ve lived the agony of failing to get it right and having to explain to my boss why that happened.

Anyway, after years of trying explain normalization, I have started to think of it in a somewhat different way. In the next few weeks, I'm going to blog some of my current thinking on what I call "Normalization for Normal People". It's an attempt to find commonalities among the kinds of tables people normally need to create for almost every database. I think I've come up with a set of very useful models for those common table types. Somewhat like the templates offered by Microsoft at Microsoft Templates, only at a more granular level.