Thursday, March 17, 2011

Database Design Fundamentals - Louis Davidson's SQL Pass 2010 Talk

I've been watching old SQL Pass talks from last year's conference. Today I watched Louis Davidson's talk, found here:

He is an interesting speaker, who talks funny because he's from Tennessee. My favorite is the pronounciation of varchar(max) as vare care (max). That rhymes, by-the-way.

His presentation covers database normalization from first normal form through Boyce/Codd normal form. He also talks about de-normalization and offers tips and techniques for good design. Forty-nine minutes into his presentation, he has a brilliant slide:

He says that if you find a lot of parsing and function calls in queries, particularly on the same column over and over again, you have strong evidence of a design that needs to be revisited. I totally agree. As soon as he said it, it hit me like a ton of bricks. If you see parsing functions over and over again, you have opened yourself up to bad data and inconsistant results. You are overly-relying on report authors and developers to get the parsing formulas right every time they touch that data. That won't happen. The database needs to be refactored so that data is easy to retrieve and store.

I've thought about this specifically, but I've never considered it as a good general rule.

Here are links for Louis Davidson:

His Website
His Blog
His Latest Book on Database Design for SQL Server 2008
His Twitter: @SqlDr